Error while retrieving records between two dates

[Dave Kreskowiak]

It's been a while since I've seen a question from you. Stop using string concatentation to build SQL queries. Use paramterized queries instead and let the OleDbParameter objects convert the dates to the proper format for you. OleDb Parameterized Queries[^]

A guide to posting questions on CodeProject[^]
Dave Kreskowiak

[Lost User]

Check this CodeProjectForums .com

[Gagan 20]

Thanks Dave for your quick reply. I used parameterised query to search record but still I'm getting same error

Try
me.listView1.Items.Clear()
Dim resultFound As Integer
DBConnection.connect()
Dim sql As String = "select * from MortgageDetails where Current_Date >= ? and Current_Date <= ?"
Dim cmd As New OleDbCommand(sql, con)
cmd.Parameters.AddWithValue("Current_Date", Convert.ToDateTime(txtDateFrom.Text))
cmd.Parameters.AddWithValue("Current_Date", Convert.ToDateTime(txtDateTo.Text))

        Dim reader As OleDbDataReader = cmd.ExecuteReader
        While reader.Read
            Dim item As New ListViewItem(New String() {reader.GetInt32(0), reader.GetString(1), reader.GetString(2), \_
                                                      reader.GetString(3), reader.GetString(4), reader.GetString(5), \_
                                                      reader.GetDateTime(6), reader.GetInt32(7), reader.GetInt32(8)})
            me.listView1.Items.Add(item)
            resultFound += 1
        End While
        reader.Close()
        lblStatus.Text = resultFound & " record(s) found!"
        'Me.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

Don't know what is wrong. :(

Gagan

[Dave Kreskowiak]

First, is this using an Access database?? Is this code running in a non-UI thread? Are your reader.Get... statements matching up EXACTLY with the types returned by the database? If you try to execute a GetInt32 on a column that is actually text, this will cause serious problems. Change your SELECT * FROM ... SQL statement to the actual column names you want returned. This will return the columns in the exact same order every time instead of relying column numbers in your Get... code. Also, you might want to use column names instead of index numbers...

A guide to posting questions on CodeProject[^]
Dave Kreskowiak

[Wayne Gaylard]

This error normally occurs when you are using a column name that is reserved keyword in Access. You don't give us any info on the column names but I would suggest you try using a full SELECT statement putting square brackets around the field names. Something like this

SELECT [Field1], [Field2], [Field3] FROM YourTable WHERE [Current_Date] BETWEEN @StartDate AND @EndDate

Hope this helps.

...and I have extensive experience writing computer code, including OIC, BTW, BRB, IMHO, LMAO, ROFL, TTYL.....

[Gagan 20]

I solved my problem. The problem was that Current_Date is reserved keyword in current Access table and I didn't wrote it in []. Now I wrote following code and It worked.

cmd.Parameters.AddWithValue("[Current_Date]", Date.Parse(txtDateFrom.Text))
cmd.Parameters.AddWithValue("[Current_Date]", Date.Parse(txtDateTo.Text))

BTW thanks for your help. :)

Gagan

[Gagan 20]

Thanks for your help. My problem has been solved. :)

Gagan

[Dave Kreskowiak]

For future reference, if you're writing an ASP.NET site or a Windows Forms app with database access from multiple threads, you don't want to use a Access database with it. Access doesn't take very well to multithreaded environments and may throw the error you posted if you do.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak

[Dave Kreskowiak]

Hmmm... Good to know. I didn't catch it because I don't use Access for anything. I always go for some flavor of SQL Server.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak

[Wayne Gaylard]

Yeah, I remembered that from years back. I now use MySQL exclusively.

...and I have extensive experience writing computer code, including OIC, BTW, BRB, IMHO, LMAO, ROFL, TTYL.....

[Dave Kreskowiak]

Jesus H Christ... Someone votes me a 2 for this?? Do I REALLY have to dig up the documentation behind this statement?? FINE!! Here it is: .NET Framework Data Providers (ADO.NET)[^] It's in the second yellow "Note" box on the page.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak