CODEWITHSUNDEEP
mysqlvb.net
user1649537
user1649537

Reputation: 1

display more than one value member

i using vb.net. got a small problem. i pick from combobox which student_id then i need the other's text box change the data based on the selected id but the problem is the value member only read one value member.

here my code:

    Dim dastudent As New Odbc.OdbcDataAdapter("SELECT * from student ", myconnection)
    Dim dsstudent As New DataSet

    'Load data about student id into the combo box
    dastudent.Fill(dsstudent, "student")
    cboID.DataSource = dsstudent.Tables("student")
    cboID.DisplayMember = "Student_Id"
    cboID.ValueMember = "Student_Name"
    cboID.ValueMember = "Student_Tel_No"
    cboID.ValueMember = "Student_Address"
    cboID.ValueMember = "Mentor_Name"
End Sub

Private Sub cboID_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboID.SelectedIndexChanged
    Dim dastudent As New Odbc.OdbcDataAdapter("SELECT * from student  WHERE (Student_Id= '" & cboID.Text & "')", myconnection)

    txtName.Text = cboID.SelectedValue.ToString()
    txtTelNo.Text = cboID.SelectedValue.ToString()
    lboAddress.Text = cboID.SelectedValue.ToString()
    txtMentor.Text = cboID.SelectedValue.ToString()
End Sub

how do i assign the value member based on the index from database. thank in advance.

Upvotes: 0

Views: 358

Answers (2)

the_lotus
the_lotus

Reputation: 12748

I would highly suggest you start learning how to use classes, this is the perfect example. You could query the student and store them for futur use.

For a quick answer. Here's an example of what you need to do.

    Dim dastudent As New Odbc.OdbcDataAdapter("SELECT * from student ", myconnection)
    Dim dsstudent As New DataSet

    'Load data about student id into the combo box
    dastudent.Fill(dsstudent, "student")
    cboID.DataSource = dsstudent.Tables("student")
    cboID.DisplayMember = "Student_Id"
    cboID.ValueMember = "Student_Id"
End Sub

Private Sub cboID_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboID.SelectedIndexChanged
    Dim dastudent As New Odbc.OdbcDataAdapter("SELECT * from student  WHERE (Student_Id= '" & cboID.SelectedValue.ToString() & "')", myconnection)

 Dim dsstudent As New DataSet

'Load data about student id into the combo box
dastudent.Fill(dsstudent, "student")

txtName.Text = dsstudent.Tables("student").Rows(dsstudent.Tables("student").Columns("Student_Name").Ordinal).ToString()
txtTelNo.Text = dsstudent.Tables("student").Rows(dsstudent.Tables("student").Columns("Student_Tel_No").Ordinal).ToString()
lboAddress.Text = dsstudent.Tables("student").Rows(dsstudent.Tables("student").Columns("Student_Address").Ordinal).ToString()
txtMentor.Text = dsstudent.Tables("student").Rows(dsstudent.Tables("student").Columns("Mentor_Name").Ordinal).ToString()
End Sub

Upvotes: 1

Steve
Steve

Reputation: 216303

I will try to build a string with the various parts extracted from the datatable and use this combination as DisplayMember of the ComboBox, letting the Student_ID as ValueMember

Dim sqlSelect = "SELECT Student_Name || ', ' || Student_Tel_No || ', ' || Student_Address" + 
                " || ', ' || Mentor_Name As StudentInfo, Student_ID from student"
Dim dastudent As New Odbc.OdbcDataAdapter(sqlSelect, myconnection)
Dim dsstudent As New DataSet

'Load data about student id into the combo box'
dastudent.Fill(dsstudent, "student")
cboID.DataSource = dsstudent.Tables("student")
cboID.DisplayMember = "StudentInfo"
cboID.ValueMember = "Student_ID"

Then in the SelectedIndexChange event you could split the DisplayMember and extract the subparts

if cboID.SelectedItem Is Not Nothing then
    Dim row = CType(cboID.SelectedItem, DataRowView)
    Dim parts = row.Item("StudentInfo").ToString().Split(",")
    txtName.Text = parts(0).Trim()
    txtTelNo.Text = parts(1).Trim()
    lboAddress.Text = parts(2).Trim()
    txtMentor.Text = parts(3).Trim()
End If

Of course, if you need to execute research on the database then the important value id the Student_ID that could easily retrieved from the ValueMember

Dim studID = Convert.ToInt32(cboID.SelectedValue)

Forgot to say the operator || works in MySQL but you need to set sql_mode to PIPES_AS_CONCAT.
Otherwise you need the CONCAT function

"SELECT CONCAT(Student_Name, ', ', Student_Tel_No, ', ', Student_Address, " + 
                    "', ', Mentor_Name) As StudentInfo, Student_ID from student"

Upvotes: 0

Related Questions