datereader works great, dataset not so hot

[mrhedrick]

Here is my scenerio.
I am populating multiple cb with a dr. Here is the code
Try
Dim drbooknum As SqlDataReader
Dim cmSqlbooknum As New SqlCommand("MSDSSearchBy", objconnection)
cmSqlbooknum.CommandType = CommandType.StoredProcedure
If objconnection.State = ConnectionState.Closed Then objconnection.Open()
drbooknum = cmSqlbooknum.ExecuteReader()

Do While drbooknum.Read = True
ComboBoxBookNum.Items.Add(drbooknum("booknum"))
Loop
drbooknum.NextResult()

Do While drbooknum.Read = True
ComboBoxPageNum.Items.Add(drbooknum("pagenum"))
Loop

drbooknum.NextResult()

Do While drbooknum.Read = True
ComboBoxSupplier.Items.Add(drbooknum("suppliername"))
Loop

drbooknum.NextResult()

Do While drbooknum.Read = True
ComboBoxCategory.Items.Add(drbooknum("category"))
Loop
drbooknum.NextResult()

Do While drbooknum.Read = True
ComboBoxproduct.Items.Add(drbooknum("productname"))
Loop

drbooknum.NextResult()

Do While drbooknum.Read = True
ComboBoxInfo.Items.Add(drbooknum("infophone"))
Loop

drbooknum.NextResult()

Do While drbooknum.Read = True
ComboBoxEmer.Items.Add(drbooknum("emerphone"))
Loop
drbooknum.Close()
cmSqlbooknum.Dispose()

Catch er As SqlException
MessageBox.Show(er.Message)
Catch err As Exception
MessageBox.Show("An Error Occurred: " & err.ToString())
Finally
objconnection.Close()

End Try

The query is as followed:

select distinct(booknum)from msdsmain;
select distinct(pagenum) from msdsmain;
select distinct(suppliername) from msdsmain;
select distinct(category) from msdsmain;
select distinct(productname) from msdsmain;
select distinct(infophone) from msdsmain;
select distinct(emerphone) from msdsmain

The datareader parses the information great.
Each cb has distinct records from each column.

I tried to put it in a DS. I thought it may be easier to do
updates and inserts. Wrong. The querybuilder mangles the
query. Is there a way to get the DS to work?
If not I can use this for my DR
Dim upMain As New SqlClient.SqlCommand
Dim upIngredients As New SqlClient.SqlCommand
Dim daMSDSMain As New SqlClient.SqlDataAdapter
Dim daMSDSIngredients As New SqlClient.SqlDataAdapter
Try
Try
upMain.CommandText = "MSDSUpdateMain"
upMain.CommandType = CommandType.StoredProcedure
daMSDSMain.SelectCommand = upMain
upMain.Connection = objconnection
If objconnection.State = ConnectionState.Closed Then objconnection.Open()
upMain.Parameters.Add(New System.Data.SqlClient.SqlParameter("@booknum", System.Data.SqlDbType.Int, 4)).Value = (ComboBoxBookNum.Text)
upMain.Parameters.Add(New System.Data.SqlClient.SqlParameter("@pagenum", System.Data.SqlDbType.Int, 4)).Value = (ComboBoxPageNum.Text)
upMain.Parameters.Add(New System.Data.SqlClient.SqlParameter("@category", System.Data.SqlDbType.VarChar, 50)).Value = (ComboBoxCategory.Text)
upMain.Parameters.Add(New System.Data.SqlClient.SqlParameter("@suppliername", System.Data.SqlDbType.VarChar, 50)).Value = (ComboBoxSupplier.Text)
upMain.Parameters.Add(New System.Data.SqlClient.SqlParameter("@productname", System.Data.SqlDbType.VarChar, 40)).Value = (ComboBoxproduct.Text)
upMain.Parameters.Add(New System.Data.SqlClient.SqlParameter("@infophone", System.Data.SqlDbType.VarChar, 15)).Value = (ComboBoxInfo.Text)
upMain.Parameters.Add(New System.Data.SqlClient.SqlParameter("@emerphone", System.Data.SqlDbType.VarChar, 15)).Value = (ComboBoxEmer.Text)
If objconnection.State = ConnectionState.Closed Then objconnection.Open()
upMain.ExecuteNonQuery()
upMain.Parameters.Clear()
objconnection.Close()

Catch er As SqlException
MessageBox.Show(er.Message)
Exit Sub
Catch err As Exception
MessageBox.Show("MSDS Main Update Failed: " & err.ToString())
Exit Sub
End Try
any input will be greatly appreciated.
Happy Holidays to all.
M

[mendhak]

If it's working, don't try to fix it

You can use a dataset as well, but you haven't shown us the code you're using to work with the dataset. You can actually databind comboboxes with datasets, but if you're only looking to populate the comboboxes for one-time population and INSERTion into the database, your datareader method is just fine