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 didn't make any changes that I'm 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 I've received this error before it's been because the field in the database that I was trying to return was empty. This isn't the case here as the field I'm trying to return is present in every database record.

 

Here's 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

Edited July 16, 2003 by Robby

[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 hasn't 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 it's type date. You're 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...what's 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 that's 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.