CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2012
    Posts
    2

    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.

  2. #2
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    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
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    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.

  4. #4
    Join Date
    Dec 2012
    Posts
    2

    Red face Re: how to read data from two date time?

    Quote Originally Posted by DataMiser View Post
    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
  •  





Click Here to Expand Forum to Full Width

Featured