Insert multiple combobox value into one sql column

Question

I need to insert in sql the value of two combobox but I cant seem to make it right.

Data = "INSERT INTO [Mc_Koy].[dbo].[Transaction] ([ID],[Desciption],[Amount]) VALUES ('" & txtbox_id.Text & "','" & cmbo_frm.SelectedValue & &cmbo_to.SelectedValue &"','" & txt_fare.Text & "')"

Yes that's not correct for many reasons, but I wish to know what error did you get here — Steve, Mar 13 '15 at 21:47
`"INSERT INTO [Mc_Koy].[dbo].[Transaction] ([ID],[Desciption],[Amount]) VALUES ('" & txtbox_id.Text & "','" & cmbo_frm.SelectedValue & " " & cmbo_to.SelectedValue & "','" & txt_fare.Text & "')"` — Josh Part, Mar 13 '15 at 21:50
one more question how can I add text 'To' between the two combobox value? — Ordiz, Mar 13 '15 at 21:59
Before going on, try to understand what happens if someone types in the txt_fare textbox the following text: `x'); DELETE FROM [Transaction]--` See [Little Bobby Tables](http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) — Steve, Mar 13 '15 at 22:22

Andrew Morton · Answer 1 · 2015-03-13T22:58:14.083

What you should do is have separate columns in the database for the "From" and "To" values. Also, the Amount column should be a numeric type, preferably a Decimal. You didn't say which database you are using, so I can't give an exact answer, but your SQL and associated VB code should be something like

Dim connStr = "Your SQL connection string here"
Using sqlConn As New SqlConnection(connStr)
    Dim sql As String = "INSERT INTO [Mc_Koy].[dbo].[Transaction] ([ID],[FromLocation],[ToLocation],[Amount]) VALUES (@Id, @From, @To, @Fare)"
    Dim sqlCmd As New SqlCommand(sql, sqlConn)
    'TODO: Set the .Size parameters to match those in the database. '
    sqlCmd.Parameters.Add(New SqlParameter With {.ParameterName = "@Id", .SqlDbType = SqlDbType.VarChar, .Size = 30, .Value = txtbox_id.Text})
    sqlCmd.Parameters.Add(New SqlParameter With {.ParameterName = "@From", .SqlDbType = SqlDbType.VarChar, .Size = 30, .Value = cmbo_frm.SelectedValue})
    sqlCmd.Parameters.Add(New SqlParameter With {.ParameterName = "@To", .SqlDbType = SqlDbType.VarChar, .Size = 30, .Value = cmbo_to.SelectedValue})
    sqlCmd.Parameters.Add(New SqlParameter With {.ParameterName = "@Fare", .SqlDbType = SqlDbType.Decimal, .Value = CDec(txt_fare.Text)})

    Try
        sqlCmd.ExecuteNonQuery()
    Catch ex As Exception
        MsgBox("I could not do that because " & ex.Message)
    End Try

End Using

Then when you want to display the From and To with the text "To" inbetween, you just retrieve the [From] and [To] values from the database and concatenate them in a string with " To " inbetween, e.g.

Dim journey As String = String.Format("{0} To {1}", fromLocation, toLocation)

is there another way to do it? I solved the two combobx value but I want to add text between them "To" but how can i do it? Data = "INSERT INTO [Mc_Koy].[dbo].[Transaction] ([ID],[Desciption],[Amount]) VALUES ('" & txtbox_id.Text & "','" & cmbo_frm.SelectedValue & " " &to&t " " & cmbo_to.SelectedValue & "','" & -txt_fare.Text & "')" — Ordiz, Mar 13 '15 at 22:32
@Ordiz If you think about it a bit, you don't really want to combine the from and to parts *in the database*. At some time, you may well be asked "How many journeys are there to Scenes Nightclub?" - if you have the froms and tos mixed in together, it would be a right pain to separate them out again. It is trivial to combine them in the VB code, as I have shown. I'm trying to encourage you to do it a better way. — Andrew Morton, Mar 13 '15 at 22:55
@Ordiz I apologise: I forgot to write two separate columns for the from and to in the database table in the code example. — Andrew Morton, Mar 13 '15 at 22:59

Insert multiple combobox value into one sql column

1 Answers1