[RESOLVED] Populating combo box where a specfic value is met?

[VintageCoro]

Hey everyone

I was wondering whether someone could show me a way of populating a combo box where a specific value is met.



I want to fill the combo box with the MDModel_Name value Where the UserID is equal to MDUser_ID. That way this means that only the models that have been uploaded by the owner are shown. Below is the form:



Thank you for reading

[circuits2]

Are you asking for the proper SELECT statement or how to bind your combobox to a datatable?

[circuits2]

It might be easier if you show us the code you have so far, and we can help you make it work.

[VintageCoro]

 Public Sub fillModelName()
        'links to database
        link_to_database()


        Dim cmd As SqlCommand = New SqlCommand("SELECT MDUser_ID FROM MDModels WHERE MDUser_ID='" & DatabaseModule.UserID & "'", DatabaseModule.con)
        DatabaseModule.con.Open()
        Dim myDA As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim myDataSet As DataSet = New DataSet()
        myDA.Fill(myDataSet, "MDModels")

        ddbCurrentModel_EditModel.DataSource = myDataSet.Tables("MDModels").DefaultView
        ddbCurrentModel_EditModel.ValueMember = "MDModel_Name"
        ddbCurrentModel_EditModel.DisplayMember = "MDUser_ID"

        DatabaseModule.con.Close()

This is my code so far, but all it returns in the combo box is "System.Data.DataRowView". If I try and catch it I get the error message "Cannot bind to the new display member. Parameter name: newDisplayMember". If the SQL statement is:

  Dim cmd As SqlCommand = New SqlCommand("SELECT MDUser_ID FROM MDModels, DatabaseModule.con)

as expected it populates the combo box, however it is populated with every item (MDModel_Name) in MDModels instead of just the ones that are owned by the user (which is decided where the UserID = MDUser_ID)

[dunfiddlin]

You're comparing an integer value with a string value in MDUser_ID='" & DatabaseModule.UserID & "'"

[circuits2]

First, you need to adjust your Display Member. Since you are only interested in the MDModel_Name, Then you should do this:

ddbCurrentModel_EditModel.ValueMember = "MDModel_Name"
ddbCurrentModel_EditModel.DisplayMember = "MDModel_Name"

As for your SELECT statement, you are actually returning all Models because you cannot concatenate parameters into a SELECT statement. You have to add them to the command:

Dim cmd As New SqlCommand
cmd.CommandText = "SELECT MDModel_Name FROM MDModels WHERE MDUser_ID = @user"
cmd.Parameters.Add("@user", SqlDbType.Integer)
cmd.Parameters("@user").Value = DatabaseModule.UserID
cmd.Connection = DatabaseModule.con

[VintageCoro]

Thank you, it worked! The only thing I had to change was the "SqlDbType.Integer" to "SqlDbType.Int". Great code and I understand how it works. I wanted to try and use parameters before but I've never used them before. Thank you 'Circuits2'

and thank you 'dunfiddlin', I haven't slept much because I've been trying to get this finished, and I miss stupid mistakes like that.

[VintageCoro]

Out of curiosity could you show me some sample code on how you would fill the other text boxes/drop down lists where the Model name AND UserID are met please?

[circuits2]

You would need to change your SELECT statement to SELECT * instead of SELECT MDModel_Name so the other information is available in your dataset.

Private Sub ddbCurrentModel_EditModel_SelectedIndexChanged (sender As Object, e As EventArgs) Handles ddbCurrentModel_EditModel.SelectedIndexChanged
 
If Not Me.ddbCurrentModel_EditModel.SelectedIndex = -1 Then
 
 For Each dr As DataRow In MyDataSet.Table(0).Rows()
 
    If dr.Item("MDModel_Name") = ddbCurrentModel_EditModel.SelectedValue Then
 
        Me.AnotherFieldTextBox.Text = dr.Item("AnotherField").ToString()
 
    End If
 
 Next
 
End If
 
End Sub

[VintageCoro]

Is it possible to update using parameters as well? I would like to be able to update the database with the new data "Where MDModel_Name = Old name AND MDUser_ID = DatabaseModule.UserID"?

[VintageCoro]

Since I set this as resolved, I've set up a new post here (http://www.vbforums.com/showthread.p...59#post4382259), could you check it out please, and thank so much for helping me!