Exception of type 'System.Data.OleDb.OleDbException'

[JH82]

Hi guys, my first post on here and i'm relatively new to VB.net so go easy on me please


I am doing a simple program to display some database records and at the moment i am writing code in a form load event procedure to populate a combobox from the database. My code looks like......

Imports System.Data
Imports System.Data.OleDb

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim idConnectionString As String = "Provider=Microsoft.JET.OLEDB.4.0;data source=C:\Documents and Settings\Preferred Customer\My Documents\MCAD-MCSD\Doughnut Shop\bin\TheDoughnutShop.mdb"

Dim idConnection As OleDbConnection = New OleDbConnection(idConnectionString)

Dim sqlString As String = "SELECT * FROM Customers"

Dim idAdapter As OleDbDataAdapter = New OleDbDataAdapter(sqlString, idConnection)

Dim idDataSet As DataSet = New DataSet

idAdapter.Fill(idDataSet, "Customers")

cbCustomerID.DataSource = idDataSet.DefaultViewManager





It is throwing the excpetion "An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll" at the line i have highlighted in red.

any ideas?


(apologies if this is in the wrong place by the way)

jim

 

[JH82]

I managed to solve this one guys, turns out my connection string data source was slightly wrong. moved the file and changed the code.

Problem is, now, the combo box populates with 6 items (there are 6 items in the database) but instead of 1,2,3,4,5,6 as it should be, each item is "System.Data.DataRowView "

Any ideas where i'm going wrong?

updated code........

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim idConnectionString As String = "Provider=Microsoft.JET.OLEDB.4.0;data source=C:\The Doughnut Shop.mdb"
Dim idConnection As OleDbConnection = New OleDbConnection(idConnectionString)
Dim sqlString As String = "SELECT CustomerID FROM Customers"
Dim idAdapter As OleDbDataAdapter = New OleDbDataAdapter(sqlString, idConnection)
Dim idDataTable As DataTable = New DataTable("Customers")
Try
idAdapter.Fill(idDataTable)
Me.cbCustomerID.DataSource = idDataTable
Catch ex As Exception
MsgBox(ex.Message)
End Try

End Sub

 

[Arg81]

each item is "System.Data.DataRowView "

you haven't set your combobox settings right.

Needs to be along the lines of

with me.combobox1
   .DataSource = me.dataset1.customer
   .DisplayMember = "CustomerName"
   .ValueMember = "CustomerID"
   .DataBindings.Add("SelectedValue", dataset1, "Customer.CustomerID") [COLOR="Green"]'only use this line if you need to bind the selected "lookup" value to your main table[/COLOR]
End With

If you are doing it in code, or you can set these in the VS GUI.

 

[JH82]

Thanks Arg, i have tried it a different way and got it working using a DataReader.

Unfortunately i'm now having trouble with databinding but i'm sure i'll get there

 

[cjard]

You should have followed Arg's advice. DataReader is not the right thing to use for this job, as youre only going to be using a DataReader to download the data into some container. Have a read of the "DataSet or DataReader?" sticky thread to better understand what each is for.


That said, I get the feeling youre using this combobox to navigate your customers, in which case you WONT want to follow arg's advice. Instead you'll want your query to be:

SELECT * FROM customers

The combo will be bound to the default view of the customers table:
cbo.DataSource = idDAtaset.Tables("Customers").DefaultView, and the display member will be "Customer ID"

Changing the selected item in the combo will cause all bound fields to update to show that chosen row.

 

[JH82]

That said, I get the feeling youre using this combobox to navigate your customers, in which case you WONT want to follow arg's advice. Instead you'll want your query to be:

SELECT * FROM customers

The combo will be bound to the default view of the customers table:
cbo.DataSource = idDAtaset.Tables("Customers").DefaultView, and the display member will be "Customer ID"

Changing the selected item in the combo will cause all bound fields to update to show that chosen row.

That's exactly what I am trying to do cjard, thanks. That's been a real help. I am making progress now. Much appreciated

 

[cjard]

That's exactly what I am trying to do cjard, thanks. That's been a real help. I am making progress now. Much appreciated

You should be aware that it is not sensible to load more than 20 items into a combo box. Item counts in excess of this will frustrate and annoy the user. If your system will hold thousands of customers, you should consider providing a search, with the results going to a grid so the user can assess which to view.

 

[JH82]

It's only a small program. Basically, there are 6 Customer ID's loaded into a combobox in the form load. There are 6 text fields that must then draw customer details from a database depending on which ID is selected in the combobox.

Got down to the rowfilter part and hit a brick wall again. Can't seem to get it to work

This is what i have so far in the "selectedindexchanged" event procedure. does anything stick out like a sore thumb?



Dim i As Short
For i = 0 To 5
If cbCustomerID.SelectedIndex = i Then
txtFirstName.DataBindings.Add("Text", DsCustomers1.Tables("Customers"), "FirstName")
txtFirstName.DataBindings.Remove(txtFirstName.DataBindings("Text"))
txtLastName.DataBindings.Add("Text", DsCustomers1.Tables("Customers"), "LastName")
txtLastName.DataBindings.Remove(txtLastName.DataBindings("Text"))
txtCity.DataBindings.Add("Text", DsCustomers1.Tables("Customers"), "City")
txtCity.DataBindings.Remove(txtCity.DataBindings("Text"))
txtState.DataBindings.Add("Text", DsCustomers1.Tables("Customers"), "State")
txtState.DataBindings.Remove(txtState.DataBindings("Text"))
txtZipCode.DataBindings.Add("Text", DsCustomers1.Tables("Customers"), "ZipCode")
txtZipCode.DataBindings.Remove(txtZipCode.DataBindings("Text"))

DsCustomers1.Tables("Customers").DefaultView.RowFilter = "CustomerID = '" & DsCustomers1.Tables("Customers").Rows(cbCustomerID.SelectedIndex)("CustomerID") & "'"
End If
Next

 

[cjard]

This is what i have so far in the "selectedindexchanged" event procedure. does anything stick out like a sore thumb?

Other than it's nearly 100% unworkable and totally not the way it would be done? No..

It really is as simple as:

Download all rows into your datatable(dataset)
Set the combo datasource to be the default view of the datatable
Set the combo display member to be the name of the column

Thats it. The combo will scroll the view which will update all controls bound to that table.

I dont work with 1.1, but you can find the info you need in the DW1 link of my signature. Upgrading to VB 2005 would be a great move here, because it makes this even easier than that I described in 3 steps above

 

[JH82]

Other than it's nearly 100% unworkable and totally not the way it would be done? No..

LOL. Hahaha no wonder it's been giving me a headache for 7 days.

Turns out the people who advised me to go about it this way were wrong then.


So literally all i need to do is bind the textboxes to the same datasource as the combobox and ensure the combobox is displaying the "CustomerID"

Apologies if i seem thick, this is my first encounter with this sort of thing and i'm finding it a bit tricky

 

[Arg81]

So literally all i need to do is bind the textboxes to the same datasource as the combobox and ensure the combobox is displaying the "CustomerID"

Yes. As long as everything you bind points to the same source, when you change the value of the combobox, the textboxes will update to match that row. Your display member would be CustomerName if you wanted that to be shown instead of the ID.

That said, I get the feeling youre using this combobox to navigate your customers, in which case you WONT want to follow arg's advice. Instead you'll want your query to be:

What was different from what I said to your reply later on? That's confused me - I use the way I've posted, so obviously I'm doing something wrong as well!!
My reply will allow the navigation of the customers as long at the rest of the textboxes / labels / controls all point to that same datasource.
If it was to be used for navigation, you just wouldn't have the databindings.add("SelectedValue".......) line - which is why it's commented to the side

hmm, can you let me know why you think that won't work, as I say, that's what I read from MS on how to bind comboboxes way back in 1.1 , and assumed 2.0 would be the same

 

[cjard]

LOL. Hahaha no wonder it's been giving me a headache for 7 days.

Turns out the people who advised me to go about it this way were wrong then.

Much as I decline to say that someone is completely wrong, I can think of no reason why anyone would add a binding then immediately remove it again..

So literally all i need to do is bind the textboxes to the same datasource as the combobox and ensure the combobox is displaying the "CustomerID"

Yes. In .NET 2.0 we use a thing called BindingSource to track position within the data. You dont have this, and must rely on either a currency manager, and/or a dataview. Tables have a default dataview which is what actually gets bound when you say:

combo.DataSOurce = myDataTable


If youre new to datasets and datatables, have a read of the ANO link in my signature

 

[cjard]

What was different from what I said to your reply later on? That's confused me - I use the way I've posted, so obviously I'm doing something wrong as well!!

You bound Customers.CustomerID to .SelectedValue
When a combo has its text/selectedXXX bound, it cannot be used for nav, only editing

In this case, it was for nav. Your code wouldnt have navigated, or it may have run the risk of editing the data it was navigating

My reply will allow the navigation of the customers as long at the rest of the textboxes / labels / controls all point to that same datasource.
If it was to be used for navigation, you just wouldn't have the databindings.add("SelectedValue".......) line - which is why it's commented to the side

hmm, can you let me know why you think that won't work, as I say, that's what I read from MS on how to bind comboboxes way back in 1.1 , and assumed 2.0 would be the same

TO be honest, I didnt read the post *that* closely.. I wouldnt ever advocate binding a combo to the same table as it was looking its values up from though.. I know you mentioned this in comment, but the comment didnt make it clear that this entire code as it stands, shouldnt be used..

Your code is entirely correct for 2 situations, perhaps you could have made it more evident that these situations are mutually exclusive, by offering a post with 2 code boxes.. Sorry.

 

[Arg81]

thats OK - thought I was doing something wrong - was prepared to edit all my applications!

 

[JH82]

Really appreciate your help on this one guys, i have now solved all the problems i was having and managed to do the code for displaying order details in a datagrid on a second form dependent on which customerID was selected and which row on the first datagrid was double clicked.



It seems to be working ok. here's the code.........



Dim dvCustomers As DataView
Dim dvOrders As DataView
Dim dvItems As DataView


Private Sub frmOrders_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load


doughnutConnection.Open()
daCustomers.Fill(DsCustomers1)
daOrders.Fill(DsOrders1)
daItems.Fill(DsItems1)
doughnutConnection.Close()

dvCustomers = New DataView
dvCustomers.Table = DsCustomers1.Tables("Customers")
cbCustomerID.DataSource = dvCustomers
cbCustomerID.DisplayMember = "CustomerID"

txtFirstName.DataBindings.Add("Text", dvCustomers, "FirstName")
txtLastName.DataBindings.Add("Text", dvCustomers, "LastName")
txtCity.DataBindings.Add("Text", dvCustomers, "City")
txtState.DataBindings.Add("Text", dvCustomers, "State")
txtZipCode.DataBindings.Add("Text", dvCustomers, "ZipCode")


End Sub

Private Sub cbCustomerID_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cbCustomerID.SelectedIndexChanged

dvOrders = New DataView
dvOrders.Table = DsOrders1.Tables("Orders")
dvOrders.RowFilter = "CustomerID = '" & cbCustomerID.Text & "'"
dgOrders.DataSource = dvOrders

End Sub

Private Sub dgOrders_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgOrders.DoubleClick

Dim OrderDetails As New frmOrderDetails

dvItems = New DataView
dvItems.Table = DsItems1.Tables("Items")
Dim orderID As Short = dgOrders.Item(dgOrders.CurrentRowIndex, 1)
dvItems.RowFilter = "orderID= " & orderID & " "
OrderDetails.dgOrderDetails.DataSource = dvItems
OrderDetails.ShowDialog()







End Sub