-
December 13th, 2012, 04:04 PM
#1
how to read data from two date time?
I need to read record dataset from two date time in a ACCESS DATABASE. the program is given below
-----------------------------------------------------------------------------------------------------------------------
kname = Path & "\DATABASE\HYDROMETEO.mdb"
cn.Provider = "Microsoft.Jet.OLEDB.4.0;"
cn.Open kname
Set cmd.ActiveConnection = cn
rs1.CursorLocation = adUseClient
bname = "Rain"
SQL = "select R1, R2 from " & bname & " where DT1 between " & CStr(DateBegin) & " and " & CStr(DateOver) & " order by DT1 asc"
rs1.Open SQL, cn
-----------------------------------------------------------------------------------------------------------------------
however, I cannot fulfill this task. where DateBegin & DateOver is date time format such as 1900/07/01 12:30:00. If I deleted the time in the date time format, it runs normally. So I wonder if anyone can give me some advices on this error. Thank you.
-
December 13th, 2012, 09:40 PM
#2
Re: how to read data from two date time?
Use PARAMETERS instead of whatever the user inputs. Otherwise, he can add a few crafted things to give unwanted results. Look at the difference below:
Code:
Option Explicit
Private Sub Form_Load()
ParamAdd
End Sub
Sub ParamAdd()
Dim cmdSQLInsert As ADODB.Command
Set cmdSQLInsert = New ADODB.Command
'Create the query
cmdSQLInsert.CommandText = "Insert Into Table1(ID, NAME, AGE) Values(?,?,?)"
cmdSQLInsert.CommandType = adCmdText
cmdSQLInsert.Prepared = True
'Create the parameters
'in this case we will create three parameters
'-----Param 1 (for Field ID)-------------
Dim gParam As ADODB.Parameter
Set gParam = New ADODB.Parameter
With gParam
.Name = "ID"
.Direction = adParamInput
.Type = adChar
.Size = 10
.Value = "xxxxxxxxxx"
End With
cmdSQLInsert.Parameters.Append gParam
'-----Param 2 (for Field Name)-------------
Set gParam = Nothing
Set gParam = New ADODB.Parameter
With gParam
.Name = "NAME"
.Direction = adParamInput
.Type = adVarChar
.Size = 50
.Value = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
End With
cmdSQLInsert.Parameters.Append gParam
'-----Param 3 (for Field AGE)-------------
Set gParam = Nothing
Set gParam = New ADODB.Parameter
With gParam
.Name = "AGE"
.Direction = adParamInput
.Type = adChar
.Size = 2
.Value = "xx"
End With
cmdSQLInsert.Parameters.Append gParam
'Set the connection property of the command object
Set cmdSQLInsert.ActiveConnection = mySQLConnection
'pass the values that need to be inserted to specific parameters that we created above
cmdSQLInsert("ID") = txtID.Text
cmdSQLInsert("NAME") = txtID.Text
cmdSQLInsert("AGE") = txtAge.Text
'Execute the command
cmdSQLInsert.Execute
End Sub
-
December 14th, 2012, 04:49 AM
#3
Re: how to read data from two date time?
If the fields are date type in the mdb then you must use # signs around them in MS Access
Code:
SQL = "select R1, R2 from " & bname & " where DT1 between #" & CStr(DateBegin) & "# and #" & CStr(DateOver) & "# order by DT1 asc"
Always use [code][/code] tags when posting code.
-
December 14th, 2012, 04:41 PM
#4
Re: how to read data from two date time?
Originally Posted by DataMiser
If the fields are date type in the mdb then you must use # signs around them in MS Access
Code:
SQL = "select R1, R2 from " & bname & " where DT1 between #" & CStr(DateBegin) & "# and #" & CStr(DateOver) & "# order by DT1 asc"
Thank you. I got it.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|