query combo box woes

[mrhedrick]

Hello,
Here is my scenerio,
I am trying to fil prefill multiple comboboxes.
All the data is from a single table

select distinct(booknum)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

I need to fill 6 comboboxes with the data from each query. I tried to make all the queries into a single string:
Dim strSQLbooknum As String = "select distinct(booknum)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"

create adapter
Dim SQLbooknum As New SqlDataAdapter(strSQLbooknum, objconnection)

fill dataset
SQLbooknum.Fill(ds, "msdsmain")

fill comboboxes with ds.

With ComboBoxBookNum
.DataSource = ds.Tables("msdsmain")
.ValueMember = "booknum"
.DisplayMember = "booknum"
End With

With ComboBoxSupplier
.DataSource = ds.Tables("msdsmain")
.ValueMember = "suppliername"
.DisplayMember = "suppliername"
End With

With ComboBoxCategory
.DataSource = ds.Tables("msdsmain")
.ValueMember = "category"
.DisplayMember = "category"
End With

With ComboBoxproduct
.DataSource = ds.Tables("msdsmain")
.ValueMember = "productname"
.DisplayMember = "productname"
End With

With ComboBoxInfo
.DataSource = ds.Tables("msdsmain")
.ValueMember = "infophone"
.DisplayMember = "infophone"
End With

With ComboBoxEmer
.DataSource = ds.Tables("msdsmain")
.ValueMember = "emerphone"
.DisplayMember = "emerphone"
End With


First combobox fill great but when trying to fill the rest it tells me it cannot bind the new display members.
If there is a solution to this or if you have a better approach,
PLEASE feel free to to make a suggestion.

Thanks and happy Holidays to all.
Mike

 

[ManicCW]

i think you should create dataset with multiple tables and then bind each combo to its table. it much cleaner approach.

 

[mrhedrick]

ok, but still same issue

I ended up doing just that ManicCW. I created table aliases:

Try
If objconnection.State = ConnectionState.Closed Then objconnection.Open()
Dim strSQLbooknum As String = "select distinct(booknum)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 "



Dim SQLbooknum As New SqlDataAdapter(strSQLbooknum, objconnection)


SQLbooknum.Fill(ds, "table")
SQLbooknum.Fill(ds, "table1")
SQLbooknum.Fill(ds, "table2")
SQLbooknum.Fill(ds, "table3")
SQLbooknum.Fill(ds, "table4")
SQLbooknum.Fill(ds, "table5")

Dim count As Integer
count = ds.Tables("table").Rows.Count 'countcombo = ComboBoxCategory.Items.Count
count = ds.Tables("table1").Rows.Count
count = ds.Tables("table2").Rows.Count
count = ds.Tables("table3").Rows.Count
count = ds.Tables("table4").Rows.Count
count = ds.Tables("table5").Rows.Count


With ComboBoxBookNum
.DataSource = ds.Tables("table")
.ValueMember = "booknum"
.DisplayMember = "booknum"
.SelectedIndex = -1
End With

With ComboBoxSupplier
.DataSource = ds.Tables("table1")
.ValueMember = "suppliername"
.DisplayMember = "suppliername"
.SelectedIndex = -1
End With

With ComboBoxCategory
.DataSource = ds.Tables("table2")
.ValueMember = "category"
.DisplayMember = "category"
.SelectedIndex = -1
End With

With ComboBoxproduct
.DataSource = ds.Tables("table3")
.ValueMember = "productname"
.DisplayMember = "productname"
.SelectedIndex = -1
End With

With ComboBoxInfo
.DataSource = ds.Tables("table4")
.ValueMember = "infophone"
.DisplayMember = "infophone"
.SelectedIndex = -1
End With

With ComboBoxEmer
.DataSource = ds.Tables("table5")
.ValueMember = "emerphone"
.DisplayMember = "emerphone"
.SelectedIndex = -1
End With

Catch er As SqlException
MessageBox.Show(Err.Description)
Catch err As Exception
MessageBox.Show("An Error Occurred: " & err.ToString())

Finally
objconnection.Close()
newMSDS = False
End Try

 

[mrhedrick]

oops, hit enter by accident

I still get the blanks in the cb after populating them with the ds. Any other thoughts?
Thanks
Mike

 

[ManicCW]

you are not hearing me. break select sql string into multiple strings and populete each table with one.