Populating text boxes via Combobox and SQL database table

Question

I am trying to populate a set of textboxes from a combobox on a form. The combo box is populated using a dataset when the form loads. When this is loaded it needs to show only one entry per unit number in the kitcombobox (which is a unit number for a kit with multiple pieces of equipment in it) but display the multiple pieces of equipment's information in the different text boxes when the unit number is selected via the kitcombobox. What approach should I take towards this? I'm really lost and this is all I have so far :(

 Private Sub ckunit()
    Dim ds As New DataSet
    Dim cs As String = My.Settings.MacroQualityConnectionString
    Dim kitcombobox As String = "SELECT DISTINCT Unit_Number, Status FROM Calibrated_Equipment WHERE CHARINDEX('CK', Unit_Number) > 0 AND Status='" & ckstatuscombbx.Text & "'"
    Dim sqlconnect As New SqlConnection(cs)
    Dim da As New SqlDataAdapter(kitcombobox, sqlconnect)

    sqlconnect.Open()
    da.Fill(ds, "Calibrated_Equipment")

    sqlconnect.Close()

    kitcombbx.DataSource = ds.Tables(0)
 End Sub

score 0 · Accepted Answer · edited May 23 '17 at 11:57

Assuming you are using WinForms, I think the key will be adding an event handler for the SelectionChangedCommitted event on kitcombbx.

You can then checked the properties on the combobox to check what is selected and run another query to pull equipment information for that kit. It'd probably look something like this:

Private Sub kitcombbx_SelectionChangeCommitted(sender As Object, e As EventArgs) _
  Handles kitcombbx.SelectionChangeCommitted

  Dim kit = kitcombbx.SelectedItem.ToString()
  Dim kitEquipment = FetchKitEquipmentInformation(kit)
  PopulateEquipmentInformation(kitEquipment)
End Sub

The way you're currently constructing your query (by concatenating string parameters directly from user input) results in bad performance for most database systems, and moreover, is a huge security vulnerability. Look up SQL injection for more detail (or read these two questions).

Better DB code would probably look something like this:

Dim query = New StringBuilder()
query.AppendLine("SELECT DISTINCT Unit_Number, Status     ")
query.AppendLine("FROM Calibrated_Equipment               ")
query.AppendLine("WHERE CHARINDEX('CK', Unit_Number) > 0  ")
query.AppendLine("      AND Status = @STATUS              ")

Dim connection As New SqlConnection(My.Settings.MacroQualityConnectionString)
Dim command As New SqlCommand(query, connection);
command.Parameters.Add("@STATUS", ckstatuscombbx.Text);

Dim da As New SqlDataAdapter(kitcombobox, sqlconnect)
'And so on...

Your question is a bit broad (and therefore, likely off-topic for StackOverflow), see How to Ask.

Thank you very much, that is what I was looking for. My apologies that the question is so broad, I didn't know exactly what I was looking for. I will definitely read up on the SQL injection so I can try to eliminate that in my SQL. Thanks again — Kyle Moffat, Dec 03 '14 at 23:40

Populating text boxes via Combobox and SQL database table

1 Answers1