How to store checkboxlist all selected items into a database single column

Question

My objective is to input all checked items from a checkbooxlist into a single column in my database. I understand it is not a good design. However, this is the requirement.

Here is the code I use to get all the selected items from checkboxlist:

 Dim listitems As String
 listitems = ControlChars.CrLf
            For i = 0 To (chkActivities.Items.Count - 1)
                If chkActivities.GetItemChecked(i) = True Then
                    listitems = listitems & (i + 1).ToString & chkActivities.Items(i).ToString & ControlChars.CrLf
                End If
            Next

Here is the connection string and command executed to populate my table:

>

            objCon.Open()
            objCmd = New SqlCommand("insert into activity_by_customer (userID, city, personal_activities, BookingDate, price) values ( '" & frmLogin.userID & "','" & cbbCity.Text & "','" & listitems & "','" & Date.Today & "','" & lblpriceValue.Text & "' )", objCon)
            objCmd.ExecuteNonQuery()
            activitiesbycustomer.Update(Me.ResourcesDataSet.activity_by_customer)
            MsgBox("Your booking has been successful")
            objCon.Close()

However when I execute this code it crashes with an error. The error is as follows:

Incorrect syntax near 's'. Unclosed quotation mark after the character string ' )'.

This error happens to appear because of 'listitems'. Any help would be appreciated.

Answer 1

Not a problem in how you build your listitems, but in how you pass the values to the database.

Do not use string concatenation to build a sql command

objCon.Open()
objCmd = New SqlCommand("insert into activity_by_customer " & _ 
    "(userID, city, personal_activities, BookingDate, price) " & _ 
    "values (@usrID, @city, @itms, @dt, @price)", objCon)
objCmd.Parameters.AddWithValue("@usrID",frmLogin.userID)
objCmd.Parameters.AddWithValue("@city",cbbCity.Text)
objCmd.Parameters.AddWithValue("@itms", listitems)
objCmd.Parameters.AddWithValue("@dt",Date.Today)
objCmd.Parameters.AddWithValue("@price", lblpriceValue.Text)
objCmd.ExecuteNonQuery()
....

In this way, the framework code formats your values considering the presence of characters like a single quote and avoiding the consequent syntax error. Moreover, in this way you avoid Sql Injection attacks