CODEWITHSUNDEEP
.netvb.netdatetimedatagridview
Sean P
Sean P

Reputation: 949

Filtering SQL by correct time spans in vb.net

I have a table that has two time columns. One is a login time one is a logout time. I would like to be able to filter between the two, basically greater than the minimum selected and less than the maximum selected. I am using a monthly calendar to get me the spans of time but it is not working correctly. From there I would like to add the 4 columns in the table to a gridview. The date in the DB is the in following format:

2/23/2010 11:17:01 AM

I know how to get single elements from the table, or a column but not entire rows.

So i guess I have 2 problems, getting the filter for the SQL correct then binding the results to a table.

Here is my code so far:

 Dim cn As OleDbConnection
    Dim cmd As OleDbCommand
    Dim str As String
    Dim dr As OleDbDataReader
    Dim date1 As Date
    Dim date2 As Date


    If (Not SubmitNewToDB(session)) Then
        MsgBox("error")
    End If
    Try
        cn = New OleDbConnection("Provider=microsoft.Jet.OLEDB.4.0;Data Source=G:\Sean\BMSBonder3_0.mdb;")
        cn.Open()
        str = "Select BonderIdentifier, UserName, Login, Logout From [Session] Where (Login < " & MonthCalendar1.SelectionEnd _
            & " AND Logout > " & MonthCalendar1.SelectionStart & ") AND BonderIdentifier = " & session.bonderIdentifier

        cmd = New OleDbCommand(str, cn)
        dr = cmd.ExecuteReader

        While dr.Read()
            If dr.Item(0).ToString <> "" Then
                DataGridView1.Rows.Add(dr.Item(0))
            End If
        End While
        dr.Close()
        cn.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

EDIT

SLaks has answered most of my question, one problem remains. I need to figure out how to make it so the monthcalendar can select just one day and return that days values. Seems like the time is an issue since it is in the DB as stated above. Just comparing the dates is not enough.

Not sure how to adjust the date to include the time.

Upvotes: 2

Views: 575

Answers (1)

SLaks
SLaks

Reputation: 887385

You should be using parameters, like this:

str = "Select BonderIdentifier, UserName, Login, Logout From [Session] Where Login >= ? AND Login <= ? AND BonderIdentifier = ?"

cmd = New OleDbCommand(str, cn)
cmd.Parameters.AddWithValue("Start", MonthCalendar1.SelectionStart
cmd.Parameters.AddWithValue("End", MonthCalendar1.SelectionEnd)
cmd.Parameters.AddWithValue("BID", session.bonderIdentifier)

To add values for the four columns, you can write 

DataGridView1.Rows.Add(dr.Item(0), dr.Item(1), dr.Item(2), dr.Item(3))

Upvotes: 2

Related Questions