Driving me Crazy...Please Help

[liquidspaces]

This section of code had been working just fine for a good while, when today it started to generate errors during execution. I didnt make any changes that Im aware of and all variables are named as they should be.

I receive the following error: "An unhandled exception of type System.Data.OleDb.OleDbException occurred in system.data.dll"

When Ive received this error before its been because the field in the database that I was trying to return was empty. This isnt the case here as the field Im trying to return is present in every database record.

Heres the code. If you need more information please let me know.

Dim strSelected1 As String = lbDate.SelectedItem
        Dim strSelected As String = LBTo.SelectedItem

        Dim objDateDataAdapter As New OleDb.OleDbDataAdapter( _
"SELECT BILL_OF_LADING_NUM FROM Shipping WHERE SHIP_TO = " & strSelected & " AND DATE = " & _
strSelected1 & "", OleDbConnection1)
        Dim objDateCommand As New OleDb.OleDbCommandBuilder(objDateDataAdapter)
        Dim objDateDataSet As New DataSet()

        objDateDataSet.Clear()
        objDateDataAdapter.FillSchema(objDateDataSet, SchemaType.Source, "Shipping")
        objDateDataAdapter.Fill(objDateDataSet, "Shipping") // WHERE THE ERROR OCCURS
        lbBOLN.Items.Clear()

        Dim i As Integer
        Dim strCurrent1 As String

        For i = 1 To objDateDataSet.Tables("Shipping").Rows.Count
            strCurrent1 = objDateDataSet.Tables("Shipping").Rows(i - 1).Item("BILL_OF_LADING_NUM")
            lbBOLN.Items.Add(strCurrent1)
Next

 

[Robby]

is DATE of date type in the database? and you should surround it with square brackets....[DATE]

 

[liquidspaces]

This is very strange...it started working for a couple hours and then bang, the same exact thing is happening.

DATE is of type date in the database. Do you mean that I should surround it with square brackets in the SQL statement? If so, I went ahead and did that though it hasnt made any noticeable difference.

Do you know of any reason why it could be cutting in and out like this?

Thanks,
Kevin

 

[liquidspaces]

I just fooled around with the database a little bit...this works fine when DATE is of type text, but not when its type date. Youre probably onto something here Robby

 

[Robby]

switch the DATE back to date type in the table and try the following...

Dim objDateDataAdapter As New OleDb.OleDbDataAdapter( _
"SELECT BILL_OF_LADING_NUM FROM Shipping WHERE " & _
 "SHIP_TO = " & strSelected & "" & _
" AND [DATE] = #" & strSelected1 & "#", OleDbConnection1)

 

[liquidspaces]

Robby, you rock! Thanks for your help.

But I do have to ask...whats with the pound signs? Does that just indicate a date or is does it indicate any sort of special formatting such as currency or time as well? If thats the case, would I reference a field of type currency with [] as well?

 

[karimgarza]

A better way is to use dates all the time

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim sometime As System.DateTime
        sometime = Now
        ListBox1.Items.Add(Now)
        ComboBox1.Items.Add(Now)
        ComboBox1.Items.Add(sometime)
    End Sub

Store the date values in the listbox or combobox as dates and never convert them to strings because you will have problems when you get the application in a region where the dates are in a different format.

Hopes this Helps
Best of Luck

 

[Robby]

The pound sign indicates to Access that the values are date data type.