Advanced Help for Combo box Needed

[Jean Christophe Avard]

Hi! I'm designing an application that produces invoices. I have a combobox
that is populated with the Name of every client in the database. What I
would like to do is to have the combobox to display the name, and to have
the value set to the ID of the record...

Like in HTML where you have something like: <option value="ID_NUMBER(primary
key)">CLIENT_NAME</option>

so, let's say the user selects "Client #2" in the combo box, then I would be
able to call the address of this client using the ID... SELECT * FROM
tbl_clients WHERE id_client = bombobox.selectedValue ...

I don't know if I'm making myself clear, I will be around, so ask anything
if I'm not enough clear. Thank y'all, this really apprecioated!

Jean Christophe Avard!

 

[Guest]

Jean Christophe Avard,

Retrieve the client name and ID from the database and store the data in a
datatable.

Assign the datatable to the combobox'x DataSource.

Assign the client name to the combobox's DisplayMember.

Assign the client ID to the combobox's ValueMember.

In the combobox'x SelectedIndexChanged event, retrieve the ID from the
combobox's SelectedValue property.

Kerry Moorman

 

[Jean Christophe Avard]

Thank you man, but I have another q7uestion... Why do I get an error with
this:

sql = "SELECT * FROM tbl_clients WHERE id_client= " +
cboxClient.SelectedValue

I also tried

sql = "SELECT * FROM tbl_clients WHERE id_client= " +
cboxClient.SelectedValue.toString()

And it always give me an error

"An unhandled exception of type 'System.InvalidCastException' occurred in
microsoft.visualbasic.dll

Additional information: Operator is not valid for string "SELECT * FROM
tbl_clients WHERE " and type 'DataRowView'."

What is the way to go!?

 

[Guest]

Jean Christophe Avard,

It looks like the combobox's ValueMember property has not been correctly set.

Be sure that the column name you supply to the ValueMember property is
spelled exactly as in the datatable, including case.

You might post your code where you retrieve the datatable and then use it as
the combobox's data source, etc.

Kerry Moorman

 

[Phil]

Jean,

Try using an ampersand & instead of the +.

Rgds, Phil

 

[Jean Christophe Avard]

the cboxClient.SelectedValue.tostring returns System.DataRowView so I still
have an error.... I also used & instead of + and its still the same...
anyone!?


this is the code in the load event of the form, where I populate the
combobox

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


' create a connection

Dim cn As SqlConnection = New SqlConnection

' open connection

cn.ConnectionString = strConn

' create sql query

Dim sql As String

If newQuotationType = "client" Then

sql = "SELECT id_client, first_name, last_name FROM tbl_clients ORDER BY
last_name, first_name"

ElseIf newQuotationType = "supplier" Then

sql = "SELECT id_supplier, name FROM tbl_suppliers ORDER BY name"

End If

' create a data adapter

Dim da As SqlDataAdapter = New SqlDataAdapter(sql, cn)

' create a dataset

Dim ds As DataSet = New DataSet

' add table to the data set

ds.Tables.Add(New DataTable("clients"))

' fill the data adapter

da.Fill(ds.Tables("clients"))

' create a datarow

Dim dr As DataRow

' populate combo box

cboxClient.DataSource = ds.Tables("clients")

If newQuotationType = "client" Then

cboxClient.DisplayMember = "last_name"

cboxClient.ValueMember = "id_client"

Else

cboxClient.DisplayMember = "name"

cboxClient.ValueMember = "id_supplier"

End If

cn.Dispose()

End Sub






This is the code I use in the selectedValueChanged event:

Private Sub cboxClient_SelectedIndexChanged(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles cboxClient.SelectedIndexChanged

Dim conn As SqlConnection = New SqlConnection





' connection string

conn.ConnectionString = strConn

Dim sql As String

If newQuotationType = "client" Then

sql = "SELECT * FROM tbl_clients WHERE id_client= " &
cboxClient.SelectedValue

ElseIf newQuotationType = "supplier" Then

sql = "SELECT * FROM tbl_suppliers WHERE id_supplier " &
cboxClient.SelectedValue

End If

MessageBox.Show(sql)

' create a new data adapter

Dim da As SqlDataAdapter = New SqlDataAdapter(sql, conn)

' create a new dataset

Dim ds As DataSet = New DataSet

ds.Tables.Add(New DataTable("address"))

da.Fill(ds.Tables("address"))

Dim address As String = ds.Tables("address").Rows(0)("address").ToString +
vbNewLine

Dim city As String = ds.Tables("address").Rows(0)("city").ToString + " (" +
ds.Tables("address").Rows(0)("state").ToString + ")" + vbNewLine

Dim country As String = ds.Tables("address").Rows(0)("country").ToString + "
" + ds.Tables("address").Rows(0)("zipcode").ToString + vbNewLine

Dim phone As String = ds.Tables("address").Rows(0)("phone").ToString +
vbNewLine

Dim fax As String = ds.Tables("address").Rows(0)("fax").ToString

rtxtAddress.Text = address + city + country + phone + fax

 

[Guest]

Jean Christophe Avard,

First assign a value to the combobox's Displaymember.

Next, assign a value to the combobox's Valuemember.

Then assign a value to the combobox's Datasource.

The order of these steps seems to matter.

Kerry Moorman