Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: Filter Data by Date into a DataGrid

    I am totally out of my area here as I am a Database person, SQL Server, etc. I am creating a very basic front end in VB6 and have successfully created a table in SQL Server 2K. I just have a textbox, command button, adodc, and datagrid on my form. I have gotten the DataGrid to successfully display ALL records in the table but now I wish to filter by date. I have changed the Adodc's Recordsource property to adCmdText and just put a "Select * from tablename" in there (without the double quotes) just to have a statement.

    I wish to input a date into a text box and upon clicking a cmd button retrieve data that corresponds to the date entered. Real vanilla but again it is not my area of expertise.

    Also, one side question, does it matter if the data in the DateOfTrans field is a hyphenated date, ie, "11-24-2004" as opposed to "11/24/2004"?

    Here is my current code but I realize I am off somewhere:

    -----------------------------------------
    Private Sub cmdDate_Click()

    Dim inputDate As Date
    Dim pstrSQL As String

    pstrSQL = "SELECT * FROM Main_Error_Report_Count where DateOfTrans = #" & txtInputDate.Text & "#"

    End Sub

    -----------------------------------

    Thanks,

    ddave

  2. #2
    Join Date
    Nov 2004
    Posts
    108

    Lightbulb DateTimePicker Control and single quotes

    as far as i'm concern SQL uses date within single quotes '11-24-2004' and VB within pound signs #11-24-2004#

    what you send in pstrSQL is something that the SQL Server must understand not VB so if you replace the pound signs to single quotes it should work.

    Also remember users are you're worst enemy
    Program defensively though there are many date formats SQL accepts you should always validate the date first. Well in my opinion validating dates is too much work so what I do is that I use a DateTimePicker Control
    to err is human ; to really mess things up requires a computer

  3. #3
    Join Date
    Feb 2004
    Posts
    193
    This still doesn't work. I modified it to the code below. Anyone know what's wrong?

    ddave

    --------------
    Private Sub cmdDate_Click()

    'Dim inputDate As Date
    Dim pstrSQL As String

    pstrSQL = "SELECT * FROM Main_Error_Report_Count where DateOfTrans = '" & txtInputDate.Text & "'"
    dgdCount.Refresh

    End Sub

  4. #4
    Join Date
    Nov 2004
    Posts
    108

    refresh the recordset first

    Code:
    pstrSQL = "SELECT * FROM Main_Error_Report_Count where DateOfTrans = '" & txtInputDate.Text & "'"
    dgdCount.Refresh
    are this line together? No other lines in between?
    you need to update first the recordset from which the data grid is getting the data from. something like:
    Code:
    if rs.state = adstateopen the rs.close
    rs.open pstrSQL
    dgdCount.Refresh
    to err is human ; to really mess things up requires a computer

  5. #5
    Join Date
    Feb 2004
    Posts
    193
    The code is exactly as I copied and pasted it. I will modify it and re-try tomorrow morning. Thanks again.

    ddave

  6. #6
    Join Date
    Feb 2004
    Posts
    193
    I'm not sure what you mean by updating the recordset where the datagrid is getting the data from. I mean isn't it just pulling this data filtered by the date I enter into the application? Sorry, but I am a real beginner w/ VB. I added the snippet you mentioned but it is now looking for an object?

    This is all the code I have:

    Code:
    Private Sub cmdDate_Click()

    'Dim inputDate As Date
    Dim pstrSQL As String

    pstrSQL = "SELECT * FROM Main_Error_Report_Count where DateOfTrans = '" & txtInputDate.Text & "'"
    If rs.State = adStateOpen Then rs.Close
    rs.Open pstrSQL
    dgdCount.Refresh

    End Sub

  7. #7
    Join Date
    Feb 2004
    Posts
    193
    I got it to work! I was missing quite a bit. The only question out of curiosity is what is the effect of "Persist Security Info = " False vs True?

    The complete code is below. Any constructive criticism is welcome. Thanks.

    ddave

    ----------------------

    Private Sub cmdDate_Click()
    Dim rs As New ADODB.Recordset
    Dim cn As New ADODB.Connection
    cn.ConnectionString = "Provider=SQLOLEDB.1;Password=just4now;Persist Security Info=True;User ID=tempsa;Initial Catalog=cmd_test;Data Source=ROSSQL20"

    cn.Open

    Dim pstrSQL As String

    pstrSQL = "SELECT * FROM Main_Error_Report_Count where DateOfTrans = '" & txtInputDate.Text & "'"
    'If rs.State = adStateOpen Then rs.Close
    rs.CursorLocation = adUseClient
    rs.Open pstrSQL, cn, adOpenStatic, adLockOptimistic
    Set dgdCount.DataSource = rs
    'dgdCount.Refresh

    End Sub

  8. #8
    Join Date
    Nov 2004
    Posts
    108
    ok lets go from the start

    Have you been able to populate the grid before trying to filter the data?

    If so, how did you populate the grid (post your code)?

    If not, are u using a data control? Are you following an example (can you post it)?
    to err is human ; to really mess things up requires a computer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •