Datagridview SQL from Access Blank

[akamini]

Hi

This is my first time posting after daniweb has helped me alot but I couldn't find a solution for this so I thought hey why not ask.

Basically I would like to have an sql statement which selects all the bookings from a table in microsoft access callled tblBookings where a certain date (selected by a datetimepicker) selected in vb.net.

The field the date is in access is bookingsdate and that is a shortdate aswell.

The problem is that even if the dates are the same the headers come up in the datagridview but no data.

This is my code:

Dim con As New OleDb.OleDbConnection
        Dim OleDBCon As System.Data.OleDb.OleDbConnection
        Dim ds As New DataSet
        Dim da As System.Data.OleDb.OleDbDataAdapter
        Dim sql As String
        Dim target2 As String

        Dim Target As String
        Dim Target3 As Date
        Dim target4 As Date


        Target = txtSearch.Text
        target2 = cmbFieldName.Text
        Target3 = dtpSearch.Value
        target4 = Format(Target3, "dd/MM/yyyy")

        If target2 = "" Then
            MsgBox("Please select a the type of information you are searching for.")
            Exit Sub
        End If

        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = \DrivingSchool.mdb"

        MsgBox(target4)
        If target2 = "Student ID" Then sql = "SELECT * FROM tblBookings WHERE StudentID = " & Target Else 
        If target2 = "Bookings ID" Then sql = "SELECT * FROM tblBookings WHERE BookingsID = " & Target Else 
        If target2 = "Date" Then sql = "SELECT * FROM tblBookings WHERE LessonDate = " & target4 

        OleDBCon = New System.Data.OleDb.OleDbConnection(con.ConnectionString)
        'sql is given a value by the if statements

        da = New System.Data.OleDb.OleDbDataAdapter(sql, OleDBCon)
        OleDBCon.Open()

        da.Fill(ds, "SearchedBookings")
        DataGridView1.DataSource = ds.Tables("SearchedBookings")
    End Sub

I hope someone can help me, if you need any more information just ask. Oh I am using visual studio 2008 if it makes any difference.

[akamini]

Just tested it and it doesnt seem to be a problem with the datagridview but has something to do with the date because if i search by studentid or bookings id it works.

[kvprajapati]

If target2 = "Date" Then sql = "SELECT * FROM tblBookings WHERE LessonDate =#" & target4 & "#"

OR

If target2 = "Date" Then sql = "SELECT * FROM tblBookings WHERE LessonDate = '" & target4 & "'"

PS: You must have to use parametrized query. Read SQL Injection.

[akamini]

If target2 = "Date" Then sql = "SELECT * FROM tblBookings WHERE LessonDate =#" & target4 & "#"

OR

If target2 = "Date" Then sql = "SELECT * FROM tblBookings WHERE LessonDate = '" & target4 & "'"

PS: You must have to use parametrized query. Read SQL Injection.

Thanks for the reply I had already tried the the second method but it returns 0 rows I have tried the other method you suggested and again it returns 0 rows. When I search for studentID they come up but if I use that same date and search for it it doesnt.

Could it have anything to do with time? Although they are both (datetimepicker and the access field) formatted to short date would time affect it?

[kvprajapati]

>Could it have anything to do with time?

Yes.

At line 16 - Do not change the date format.
So try,

If target2 = "Date" Then sql = "SELECT * FROM tblBookings WHERE LessonDate = '" & target3 & "'"

[akamini]

>Could it have anything to do with time?

Yes.

At line 16 - Do not change the date format.
So try,

If target2 = "Date" Then sql = "SELECT * FROM tblBookings WHERE LessonDate = '" & target3 & "'"

removed that line and also changed the thing from target4 to target3 where necessary but it still doesnt return any rows. Thanks for your help by the way it is greatly appreciated. Is there anything else I can try because i've read over it a few times and it seems as though the logic is right but it doesn't seem to work.

Also if i use the

If target2 = "Date" Then sql = "SELECT * FROM tblBookings WHERE LessonDate = '" & target3 & "'"

then it returns a datatype mismatch but the other code with the #'s doesnt return a datatype mismatch error but doesnt return any rows.

[kvprajapati]

In case, a date field contains time along with date.

select * from tableName where  cdate(format(DateCol,'mm-dd-yyyy'))=#02/11/2010#

[akamini]

I managed it to return a record using a date if I use

If target2 = "Date" Then sql = "SELECT * FROM(tblBookings)WHERE (((tblBookings.[LessonDate])=#2/4/2010#));"

But then if I try and change the 2/4/2010 to target 3 in the code

If target2 = "Date" Then sql = "SELECT * FROM(tblBookings)WHERE (((tblBookings.[LessonDate])=#" & Target3 & "#""

Then it returns the error

Missing ), ], or Item in query expression '(((tblBookings.[LessonDate])=#04/02/2010 18:28:27#'.

Obviously I need to get rid of the date but I should be able to use your previous code to do that.

[akamini]

Managed to get it working. Thanks. Here is the code for anyone who has the same problem.

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dim con As New OleDb.OleDbConnection
        Dim OleDBCon As System.Data.OleDb.OleDbConnection
        Dim ds As New DataSet
        Dim da As System.Data.OleDb.OleDbDataAdapter
        Dim sql As String
        Dim target2 As String
        Dim Target As String
        Dim Target3 As Date
        Dim target4 As Date

        Target = txtSearch.Text
        target2 = cmbFieldName.Text
        Target3 = dtpSearch.Value
        target4 = Format(Target3, "MM/dd/yyyy")

        If target2 = "" Then
            MsgBox("Please select a the type of information you are searching for.")
            Exit Sub
        End If

        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = \DrivingSchool.mdb"

        If target2 = "Student ID" Then sql = "SELECT * FROM tblBookings WHERE StudentID = " & Target Else 
        If target2 = "Bookings ID" Then sql = "SELECT * FROM tblBookings WHERE BookingsID = " & Target Else 
        If target2 = "Date" Then sql = "SELECT * FROM(tblBookings)WHERE (((tblBookings.[LessonDate])=#" & target4 & "#));"


        OleDBCon = New System.Data.OleDb.OleDbConnection(con.ConnectionString)
        'sql is given a value by the if statements

        da = New System.Data.OleDb.OleDbDataAdapter(sql, OleDBCon)
        OleDBCon.Open()

        da.Fill(ds, "SearchedBookings")
        DataGridView1.DataSource = ds.Tables("SearchedBookings")

    End Sub