[RESOLVED] Using Date variables for queries

[micki_free]

Hi all

I am having a bit of trouble trying to restrict table data using a date . I am using Microsoft Access, an OleDbConenction and OleDBCommand along with a Datagridview. My code is as follows

   Dim someDate As Date = #5/18/2008#
        MsgBox(CStr(someDate))
        OleDbCommand1.CommandText = "SELECT * FROM AccessLog WHERE Session_Start > '" + someDate + "'"
        Dim reader As OleDbDataReader = OleDbCommand1.ExecuteReader
        Dim table As New DataTable
        table.Load(reader)
        reader.Close()
        DataGridView1.DataSource = table
        DataGridView1.ReadOnly = True

any ideas on why I am getting an error?

[riteshjain1982]

what error message and at what line?

also make sure you are opening connection object as icant see this in code.

[jmcilhinney]

I think I might put this in a PM and just send it to every member because it seems like I've had to say it to every member anyway:

Do NOT use string concatenation to build SQL statements. Use parameters to insert variable values into SQL code.

Follow the Data Access link in my signature to see examples of using parameters with SQL code.

[micki_free]

Thank you for the advice jmcilhinney, hovever I'm still having problems.

When I try to restrict values by a whole number everything is fine. However when I try to restrict values by date nothing comes up (the datagridview remains blank)

my code is as follows

        OleDbCommand1.CommandText = "SELECT * FROM AccessLog WHERE Session_Start = @Session_Start"
        OleDbCommand1.Parameters.AddWithValue("@Session_Start", 18 / 5 / 2008)

        Dim reader As OleDbDataReader = OleDbCommand1.ExecuteReader
        Dim table As New DataTable
        table.Load(reader)
        reader.Close()
        DataGridView1.DataSource = table
        DataGridView1.ReadOnly = True

[jmcilhinney]

But you aren't using a date. That's not a date. That is an expression that is evaluated as 18 divided by 5 divided by 2008. This is a date:

OleDbCommand1.Parameters.AddWithValue("@Session_Start", #5/18/ 2008#)

Now, that said, if you're just going to use a literal value then there's no point using a parameter at all. You may as well just hard code the value right into your SQL code. I assume that this is just for testing though, and you'll be using a variable value for the real thing, like the Value from a DateTimePicker or the like.

[micki_free]

Thanks once again jmcilhinney. You're right it was just for testing but it's working fine now.

There's only one problem I' still having. After I've ran one search and I attempt to run another one (by pressing the button containing the code) the DataGridView just stays as it was. Is there any way to refresh it?

My code is below

        Try
            OleDbCommand1.CommandText = "SELECT * FROM AccessLog WHERE Session_Start > @Session_Start"
            OleDbCommand1.Parameters.AddWithValue("@Session_Start", DateValue(txtStartDate.Text))

            Dim reader As OleDbDataReader = OleDbCommand1.ExecuteReader
            Dim table As New DataTable
            table.Load(reader)
            reader.Close()
            DataGridView1.DataSource = table
            DataGridView1.ReadOnly = True
        Catch badcast As InvalidCastException
            MessageBox.Show(badcast.Message)
        End Try

[jmcilhinney]

First up, there should be no reason for you that have to catch an InvalidCastException there. If there's a particular reason that you want to use a TextBox instead of a DateTimePicker, which you should be using, then you can at least use Date.TryParse to convert the String to a Date without throwing an exception if it doesn't work.

As for your question, perhaps try setting the DataSource to Nothing before assigning the DataTable.

[micki_free]

Duly noted mate

Once again thanks for your advice

[jmcilhinney]

As for your question, perhaps try setting the DataSource to Nothing before assigning the DataTable.

Just note that I'm not sure whether this will work or not. At a glance it looks like your existing code should work. As always, post back if it doesn't do the job.

[techgnome]

set the datasource to nothing, and see if it also has a .Clear method... if it does, call it after setting the datasource to nothing, but before you attach another one. If it does not have a clear method.... disregard.

-tg

[micki_free]

Hi guys. I tried setting the Datasource property of the Datagridview to nothing but I am still only able to run one search at a time by pressing the button

I can not find a clear method for the Datagridview. My code is bleow

        OleDbCommand1.CommandText = "SELECT * FROM AccessLog WHERE Session_Start > @Session_Start"
        OleDbCommand1.Parameters.AddWithValue("@Session_Start", DateTimePickerStart.Value.Date)


        Dim reader As OleDbDataReader = OleDbCommand1.ExecuteReader
        Dim table As New DataTable
        table.Load(reader)
        reader.Close()

        DataGridView1.DataSource = ""

        DataGridView1.DataSource = table
        DataGridView1.ReadOnly = True

Any ideas?

[Clanguage]

Use the clear method on the rows

        DataGridView1.Rows.Clear()

[micki_free]

I tried using the following code

        OleDbCommand1.CommandText = "SELECT * FROM AccessLog WHERE Session_Start > @Session_Start"
        OleDbCommand1.Parameters.AddWithValue("@Session_Start", DateTimePickerStart.Value.Date)

        Dim reader As OleDbDataReader = OleDbCommand1.ExecuteReader
        Dim table As New DataTable
        table.Load(reader)
        reader.Close()

        DataGridView1.DataSource = ""
        DataGridView1.Rows.Clear()

        DataGridView1.DataSource = table
        DataGridView1.ReadOnly = True

and I wasn't even able to run the search once. I kept gettin an error saying

"Rows collection cannot be programmatically cleared when the DataGridView control is data-bound to anything else than an IBindingList that supports change notification and allows deletion."

[Clanguage]

I believe you need to clear your datasource first. Try this

DataGridView.DataSource = null;
DataGridView.Rows.Clear();
DataGridView.Refresh();

[micki_free]

I noticed that you assigned the datasource to null. However When I try that I get an error message in the error list

"'null' is not declared. 'Null' constant is no longer supported; use 'System.DBNull' instead."

When I try System.DBNull I get the message

"'DBNull' is a type in 'System' and cannot be used as an expression."

[jmcilhinney]

Clanguage has posted C# code. In VB this line:

DataGridView.DataSource = null;

becomes this:

DataGridView.DataSource = Nothing

[micki_free]

Still no joy I'm afraid guys. The Weird thing is that when I click on the button to run the search the Datagriview will flash (as if it is refreshing) yet the data remains the same

my code is as follows

OleDbCommand1.CommandText = "SELECT * FROM AccessLog WHERE Session_Start > @Session_Start"
        OleDbCommand1.Parameters.AddWithValue("@Session_Start", DateTimePickerStart.Value.Date)

        Dim reader As OleDbDataReader = OleDbCommand1.ExecuteReader
        Dim table As New DataTable
        table.Load(reader)
        reader.Close()

        DataGridView1.DataSource = Nothing
        DataGridView1.Rows.Clear()
        DataGridView1.Refresh()

        DataGridView1.DataSource = table
        DataGridView1.ReadOnly = True

Any further help you could give me would be much appreciated

[jmcilhinney]

Have you actually looked through the data in code to make sure you aren't simply getting the same data over and over?

[micki_free]

OK guys, I found out WHERE the problem lies. Now I just need to work out WHAT the problem is

I tried the following code after populating the Datatable/DataGridView. It populates a listbox with the data.

        'making sure the output is correct
        lstDBTest.Items.Clear()
        Dim reader2 As OleDbDataReader = OleDbCommand1.ExecuteReader
        While reader2.Read()
            lstDBTest.Items.Add(CStr(reader2.Item("Session_No")))
            lstDBTest.Items.Add(CStr(reader2.Item("Person_ID")))
            lstDBTest.Items.Add(CStr(reader2.Item("Session_Start")))
            lstDBTest.Items.Add("")
        End While
        reader2.Close()
        'making sure the output is correct

Surprisingly the exact same thing happened with the list box (the listbox refreshed but the data stayed exactly the same). So as far as I can tell the problem lies in this block of code here

        OleDbCommand1.CommandText = "SELECT * FROM AccessLog WHERE Session_Start > @Session_Start"
        OleDbCommand1.Parameters.AddWithValue("@Session_Start", DateTimePickerStart.Value.Date)

        Dim reader As OleDbDataReader = OleDbCommand1.ExecuteReader

It looks as if either the SQL query is incorrect, the executereader command only executes the first time or the reader does not change as the SQL query changes. Can you guys see anything out of the ordinary in the code?