Error while retrieving records between two dates

Gagan 20

Hi all... I am getting an error while searching records between two dates. The error message is "IErrorInfo.GetDescription failed with E_FAIL(0x80004005)." code is :

Try
Me.listView1.Items.Clear()
Dim resultFound As Integer
DBConnection.connect() 'Code to open database.
Dim sql As String = "select * from MortgageDetails where Current_Date between '" & Convert.ToDateTime(txtDateFrom.Text) & "' and '" & Convert.ToDateTime(txtDateTo.Text) & "'"
Dim cmd As New OleDbCommand(sql, con)
Dim reader As OleDbDataReader = cmd.ExecuteReader 'Getting error on this line.
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

Suggest me what should I do Thanks. Gagan

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