Best way to fill a ComboBox from SQL Table 
Author Message
 Best way to fill a ComboBox from SQL Table

(dotNet Windows Forms)
I need to populate a number of ComboBox's from SQL
Tables.  It is static information, so no refresh or update
is needed.  Also, I need to link the displayed item in the
ComboBox to an associated ID.

My question is -- is it better to use a SQLDataAdapter and
DataSet, or just use a SQLDataReader?  What are pros and
cons of each mothod?  And if using SQLDataReader, how does
one link the ID field to the Description in the ComboBox?

Example (using SQLDataAdapter):

Dim dtrDataAdapter As New SqlDataAdapter(SQLString,
conString)
Dim myDataSet As New DataSet()
dtrDataAdapter.Fill(myDataSet, "myNewTable")

ComboBox1.DataSource = myDataSet.Tables("myNewTable")
ComboBox1.DisplayMember = "Description"
ComboBox1.ValueMember = "Batch_Id"

This works fine -- but is it the right way to do it?

Example (using SQLDataReader):

cmdSelect = New SqlCommand(SQLString, conString)
dtrDataReader = cmdSelect.ExecuteReader()

Do
  Do Until dtrDataReader.Read = False
    ' this will populate the ComboBox display
    ComboBox1.Items.Add(dtrDataReader.GetString(0))
    ' how to link ID to Item ???
  Loop
Loop While dtrDataReader.NextResult

That would work fine too, if I could figure out a way to
link the ID to each Item.  Which is the best way to do
this, and how to link the ID?

Any insights / thoughts / clues would be appreciated.

Thanks.

-DND



Tue, 08 Mar 2005 01:34:54 GMT  
 Best way to fill a ComboBox from SQL Table
DND,
Unfortunately I do not have an answer for your question. But I'm working
with similar stuff and was wondering what is the purpose of ValueMember
property of ComboBox. You assign it this way:
ComboBox1.ValueMember = "Batch_Id"
Then how do you get Batch_ID from Combo?
If I need it I do not know any other way to get that value except to go back
to DataTable and select it from there  by using SelectedIndex property of
ComboBox. Something like this:

myBatch_ID =
myDataSet.Tables("myNewTable").Rows(ComboBox1.SelectedIndex)("Batch_ID")

So, how could I get that value directly from ComboBox1. If I cannot get it,
why do I need to assign ValueMember property of ComboBox1?
Thank you
VC


Quote:
> (dotNet Windows Forms)
> I need to populate a number of ComboBox's from SQL
> Tables.  It is static information, so no refresh or update
> is needed.  Also, I need to link the displayed item in the
> ComboBox to an associated ID.

> My question is -- is it better to use a SQLDataAdapter and
> DataSet, or just use a SQLDataReader?  What are pros and
> cons of each mothod?  And if using SQLDataReader, how does
> one link the ID field to the Description in the ComboBox?

> Example (using SQLDataAdapter):

> Dim dtrDataAdapter As New SqlDataAdapter(SQLString,
> conString)
> Dim myDataSet As New DataSet()
> dtrDataAdapter.Fill(myDataSet, "myNewTable")

> ComboBox1.DataSource = myDataSet.Tables("myNewTable")
> ComboBox1.DisplayMember = "Description"
> ComboBox1.ValueMember = "Batch_Id"

> This works fine -- but is it the right way to do it?

> Example (using SQLDataReader):

> cmdSelect = New SqlCommand(SQLString, conString)
> dtrDataReader = cmdSelect.ExecuteReader()

> Do
>   Do Until dtrDataReader.Read = False
>     ' this will populate the ComboBox display
>     ComboBox1.Items.Add(dtrDataReader.GetString(0))
>     ' how to link ID to Item ???
>   Loop
> Loop While dtrDataReader.NextResult

> That would work fine too, if I could figure out a way to
> link the ID to each Item.  Which is the best way to do
> this, and how to link the ID?

> Any insights / thoughts / clues would be appreciated.

> Thanks.

> -DND



Tue, 08 Mar 2005 02:07:43 GMT  
 Best way to fill a ComboBox from SQL Table
I found it myself
It can be done this way:
myBatch_ID = ComboBox1.SelectedValue
Thank you
VC


Quote:
> DND,
> Unfortunately I do not have an answer for your question. But I'm working
> with similar stuff and was wondering what is the purpose of ValueMember
> property of ComboBox. You assign it this way:
> ComboBox1.ValueMember = "Batch_Id"
> Then how do you get Batch_ID from Combo?
> If I need it I do not know any other way to get that value except to go
back
> to DataTable and select it from there  by using SelectedIndex property of
> ComboBox. Something like this:

> myBatch_ID =
> myDataSet.Tables("myNewTable").Rows(ComboBox1.SelectedIndex)("Batch_ID")

> So, how could I get that value directly from ComboBox1. If I cannot get
it,
> why do I need to assign ValueMember property of ComboBox1?
> Thank you
> VC



> > (dotNet Windows Forms)
> > I need to populate a number of ComboBox's from SQL
> > Tables.  It is static information, so no refresh or update
> > is needed.  Also, I need to link the displayed item in the
> > ComboBox to an associated ID.

> > My question is -- is it better to use a SQLDataAdapter and
> > DataSet, or just use a SQLDataReader?  What are pros and
> > cons of each mothod?  And if using SQLDataReader, how does
> > one link the ID field to the Description in the ComboBox?

> > Example (using SQLDataAdapter):

> > Dim dtrDataAdapter As New SqlDataAdapter(SQLString,
> > conString)
> > Dim myDataSet As New DataSet()
> > dtrDataAdapter.Fill(myDataSet, "myNewTable")

> > ComboBox1.DataSource = myDataSet.Tables("myNewTable")
> > ComboBox1.DisplayMember = "Description"
> > ComboBox1.ValueMember = "Batch_Id"

> > This works fine -- but is it the right way to do it?

> > Example (using SQLDataReader):

> > cmdSelect = New SqlCommand(SQLString, conString)
> > dtrDataReader = cmdSelect.ExecuteReader()

> > Do
> >   Do Until dtrDataReader.Read = False
> >     ' this will populate the ComboBox display
> >     ComboBox1.Items.Add(dtrDataReader.GetString(0))
> >     ' how to link ID to Item ???
> >   Loop
> > Loop While dtrDataReader.NextResult

> > That would work fine too, if I could figure out a way to
> > link the ID to each Item.  Which is the best way to do
> > this, and how to link the ID?

> > Any insights / thoughts / clues would be appreciated.

> > Thanks.

> > -DND



Tue, 08 Mar 2005 02:30:30 GMT  
 Best way to fill a ComboBox from SQL Table
Hey, wait a minute!  You're supposed to be answering my
questions, not asking questions of the DotNetDummy.  Since
you did though, I think I can help.

Using the code example I provided, you can retrieve
the "ID" for the selected item from the ComboBox using
something like this:

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
ComboBox1.SelectedIndexChanged

  myIDHolder = ComboBox1.SelectedValue.ToString

End Sub

That's all there is to it -- and no references back to the
database are required.

Hope that is helpful.

-DND

Quote:
>-----Original Message-----
>DND,
>Unfortunately I do not have an answer for your question.
But I'm working
>with similar stuff and was wondering what is the purpose
of ValueMember
>property of ComboBox. You assign it this way:
>ComboBox1.ValueMember = "Batch_Id"
>Then how do you get Batch_ID from Combo?
>If I need it I do not know any other way to get that

value except to go back
Quote:
>to DataTable and select it from there  by using

SelectedIndex property of
Quote:
>ComboBox. Something like this:

>myBatch_ID =
>myDataSet.Tables("myNewTable").Rows

(ComboBox1.SelectedIndex)("Batch_ID")
Quote:

>So, how could I get that value directly from ComboBox1.
If I cannot get it,
>why do I need to assign ValueMember property of ComboBox1?
>Thank you
>VC


message

>> (dotNet Windows Forms)
>> I need to populate a number of ComboBox's from SQL
>> Tables.  It is static information, so no refresh or
update
>> is needed.  Also, I need to link the displayed item in
the
>> ComboBox to an associated ID.

>> My question is -- is it better to use a SQLDataAdapter
and
>> DataSet, or just use a SQLDataReader?  What are pros and
>> cons of each mothod?  And if using SQLDataReader, how
does
>> one link the ID field to the Description in the
ComboBox?

>> Example (using SQLDataAdapter):

>> Dim dtrDataAdapter As New SqlDataAdapter(SQLString,
>> conString)
>> Dim myDataSet As New DataSet()
>> dtrDataAdapter.Fill(myDataSet, "myNewTable")

>> ComboBox1.DataSource = myDataSet.Tables("myNewTable")
>> ComboBox1.DisplayMember = "Description"
>> ComboBox1.ValueMember = "Batch_Id"

>> This works fine -- but is it the right way to do it?

>> Example (using SQLDataReader):

>> cmdSelect = New SqlCommand(SQLString, conString)
>> dtrDataReader = cmdSelect.ExecuteReader()

>> Do
>>   Do Until dtrDataReader.Read = False
>>     ' this will populate the ComboBox display
>>     ComboBox1.Items.Add(dtrDataReader.GetString(0))
>>     ' how to link ID to Item ???
>>   Loop
>> Loop While dtrDataReader.NextResult

>> That would work fine too, if I could figure out a way to
>> link the ID to each Item.  Which is the best way to do
>> this, and how to link the ID?

>> Any insights / thoughts / clues would be appreciated.

>> Thanks.

>> -DND

>.



Tue, 08 Mar 2005 02:37:21 GMT  
 Best way to fill a ComboBox from SQL Table
There is no need to use any value id's or other mechanisms. Just add an
object into the Items member.

You can simply do something the following to add an item to a combo box.
(add a complete object) This way when you get the selected index you can get
the complete object out of the combo box.

 public class ComboItem
  private m_name as string
  private m_id as integer
  public sub new(name as string, id as integer)
   m_name = name
   m_id = id
  End Sub

   overrides public function toString() as string
   return name
  End Function
  public property name as string
   get
    return m_name
   end get
   set
    m_name = value
   end set
  End Property
  public property id as integer
   get
    return m_id
   end get
   set
    m_id = value
   end set
  End Property
 End Class

   m_comboBox.Items.add(new ComboItem("name1", 1))
   m_comboBox.Items.add(new ComboItem("name2", 2))



Tue, 08 Mar 2005 02:50:26 GMT  
 Best way to fill a ComboBox from SQL Table
VC and DotNetDummy

The ComboBox in .NET is not like the VB6 one. The .NET ComboBox actually
contains references to objects which you can extract and use directly. The
DisplayMember and ValueMember properties are for your convenience.

DotNetDummy, I'd recommend using the first of your options. Set the
ComboBox's DataSource to the DataTable. Then, the ComboBox fills with
DataRow objects from your DataTable. When you fill the ComboBox using your
DataReader method you are just stuffing Strings into the ComboBox and loss
the relationship between the Description and the Batch_ID.

VC, you can get the Batch_ID from the ComboBox by using the SelectedValue
property and casting to the correct type (since SelectedValue returns an
Object). For example, if Batch_ID is an Integer:

Private Sub ComboBox1_SelectedValueChanged(sender As Object, e As EventArgs)
_
    Handles ComboBox1.SelectedValueChanged

    Dim batchID As Integer

    If Not ComboBox1.SelectedItem Is Nothing ' Test to ensure value is set
        batchID = CInt(ComboBox1.SelectedValue)
    End If
End Sub

Note however that the SelectedValue will be of type DataRowView until the
ValueMember property has been appropriately set.

Hope this helps,

Ian.



Tue, 08 Mar 2005 03:05:02 GMT  
 Best way to fill a ComboBox from SQL Table
This is cool.  Thanks.  One more question -- how do I then
retrieve these property values in the
ComboBox1_SelectedIndexChanged Sub?

I've tried a few things, and can't seem to extract the
info.

Thanks much!

-DND

Quote:
>-----Original Message-----
>There is no need to use any value id's or other

mechanisms. Just add an
Quote:
>object into the Items member.

>You can simply do something the following to add an item
to a combo box.
>(add a complete object) This way when you get the

selected index you can get
Quote:
>the complete object out of the combo box.

> public class ComboItem
>  private m_name as string
>  private m_id as integer
>  public sub new(name as string, id as integer)
>   m_name = name
>   m_id = id
>  End Sub

>   overrides public function toString() as string
>   return name
>  End Function
>  public property name as string
>   get
>    return m_name
>   end get
>   set
>    m_name = value
>   end set
>  End Property
>  public property id as integer
>   get
>    return m_id
>   end get
>   set
>    m_id = value
>   end set
>  End Property
> End Class

>   m_comboBox.Items.add(new ComboItem("name1", 1))
>   m_comboBox.Items.add(new ComboItem("name2", 2))

>.



Tue, 08 Mar 2005 05:21:13 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. need to fill a combobox from a table

2. need to fill a combobox from a table

3. I want to fill a ComboBox with tables from an Access DB

4. need to fill a combobox from a table

5. Filling a Secondary Combobox based on information from a primary combobox

6. Using SQL to fill table - missing data

7. dropdownlists and grids, which ways is the best?

8. good ways to use recordsets without autocommit?

9. Better ways to schedule?

10. good ways to use recordsets without autocommit?

11. Better ways to put text into textbox??

12. populating a combobox with data from a SQL Database table field

 

 
Powered by phpBB® Forum Software