Dataset - Please read, am I using the right object?

[Marty]

Hi,

Ok I use the OLEDBConnector and dataset to retrieve data from my Access DB.

I have a problem to read/parse the dataset and I would like to know if I
am using the right object to reach my goal. And I don't want to spend
hours to find out, would you tell me if I'm going in the wrong path?

Private dbConnector As New OleDbConnection

Private Function mySQL() as dataset
Dim strSQLSelect As String

strSQLSelect = "SELECT strName" & _
"FROM tblAdmin " & _
"ORDER BY ID ASC;"

Dim dstDataset As DataSet = New DataSet
Dim Cmd As New OleDbDataAdapter(strSQLSelect, dbConnector)
Cmd.Fill(dstDataset)

Return dstDataset
End Function

Private Sub fillComboBoxAdmin()
Dim dstDataset As DataSet
dstDataset = mySQL()
Dim i As Int32
For i = 0 To dstDataset.Tables(0).Rows.Count - 1
'Fill combobox with dataset content
cbxAdmin.Items.Add(dstDataset.Tables(0).Rows.Item(i))
Next
End Sub

Then the combobox is filled. The user can select a row in the combo box
and if the user hit the "Save" button. Another part of the code will
save the selected combobox row to the database for current user.

Am I doing a good use of the dataset? Because in the sub
fillComboBoxAdmin, I can't get anything out of the dataset.

Thanks
Marty

 

[Ken Dopierala Jr.]

Hi Marty,

You might not be getting any data into your dataset. You should put the
data code in a try catch block.

Try
....database code
Catch exc as Exception
msgbox(exc.Message)
End Try

On thing I notice is that in the first line of your Select statement it
doesn't look like there is a space between strName and the ". Putting a
space there might return some data for you. Also changing the loop in your
fill function should help. Remove these lines:

Dim i As Int32
For i = 0 To dstDataset.Tables(0).Rows.Count - 1
'Fill combobox with dataset content
cbxAdmin.Items.Add(dstDataset.Tables(0).Rows.Item(i))
Next

And Add:

Dim objDR as DataRow
For Each objDR in dstDataset.Tables(0).Rows
cbxAdmin.Items.Add(cstr(objdr("strName")))
Next

Good luck! Ken.

 

[Marty]

Thanks Ken,

With your tips I get my combobox filled with coherent data.
Thank you, you helped me a lot.

Marty

 

[Stephany Young]

I'm wondering why you are manually filling the list for the combobow when
you could just as easily set the datasource and displaymember properties of
the combobox and have it bound directly to the datatable.

 

[Cor Ligthert]

Mary,

As addition to the message from Ken (D) and Stephany when you do not know
it.
(In the shortest notation), you can change this Sub in this way)

\\\
Private Sub fillComboBoxAdmin()
cbxAdmin.datasource = mySQL()
cbxAdmin.displaymember = "TheColumName"
End sub
///
Your current routine should give an error (as Ken showed as well), the
reason is because you are looping through the rows while using everytime the
next item. Which should when you have more rows than items give an error and
probably always give a not wanted result.

I hope this helps?

Cor

 

[Marty]

Hi Stephany,

Good point, it thaught to that but I wans't sure about which way is
better. It start form this point:

1- I could implement my code in way you told me. That mean that when
the user select a row in the combobox and save, the code would have to
search fisrt the unique number (ID in DB table) of the selected item in
the combobox, then save the unique number (ID) for the current user (in
DB user table).

2- Implement the way I did and insert all items in the combobox ordered
by unique number (ID) from the SQL query. So I would know directly
which unique number (ID) to write (for the selected item) for current
user (in DB user table).

This is the first time that I build a data management application. I do
it as a personal project. I'll take your tip in consideration, because
writing it down make me think about it again and I think that point#1
should be better for data integrity. But it need one more access to DB
to have this unique number.

Am I right?

Thanks very much.

Marty

 

[Marty]

Hi Cor,

I tried it but I get the combobox filled with
"System.Data.DataViewManagerListItemTypeDescriptor"

I have to leave, but I'll double check my code when I'll be back!

Thanks for your help.

Marty

 

[Stephany Young]

To get the idea of how to achieve this I would recommend that you use the
form designer to do the initial coding work for you.

Drop an OleDbDataAdapter component on the form and configure it to do your
SQL SELECT and to create a Typed Dataset for your project. This should also
add an OleDbConnection component and a Dataset component to your form. (Just
follow your nose in configuring these components.)

Drop a Dataviw component on the form and connect it to the Datatable in your
Dataset. Set the Sort property of the dataview to ID.

For your ComboBox, set the DataSource property to <DataViewname>, set the
DisplayMember property to strName ans set the ValueMember property to ID.

In the Form_Load event, add:

<OleDbDataAdaptername>.Fill(<DataSetname>.Tables("<DataTablename>"))

In the <comboboxname>_SelectedValueChanged event, add the code to deal with
the users selection by accessing the SelectedValue property of the ComboBox
which will be the ID (ValueMember) of the selected row.

Dim _i As Integer = cbxAdmin.SelectedValue

' Deal with _i as required

Once you have been through this exercise, I would recommend that you inspect
the generated code to gain an understanding of what is actually happening.
If you so wish, you could use that code as the basis for coding it yourself
rather than using the designer components.

I further recommend that you peruse the help files for the
componets/properties/methods mentioned above so that you gain a better
understanding of how (in this case) the ComboBox is designed to be
integrated with the various data components.