How do I insert listbox data as one bulk data in a table cell in sql-server

Question

I'm trying to save list of data in listbox to the sql server as a bulk data but I don't have any idea. This is my code:

cmd As New SqlClient.SqlCommand("INSERT INTO tblStudentPersonalInformation(studentregistrationumber,studentname,studentgender,studentdob,studentpicture,studentntsubjectssat,studentfamilymembers,) VALUES('" & txtStudentRegistrationNumber.Text & "','" & txtStudentName.Text & "','" & cmbStudentGender.Text & "','" 
& studentdob & "','" & "',@studentpicture,'" & lbStudentSubjectsSat.Text & lbStudenttFamilyMembers.Text & "')", 
databaseconnection.cn)

cmd.Parameters.Add(New SqlClient.SqlParameter("@studentpicture", SqlDbType.Image)).Value = IO.File.ReadAllBytes(openfile.FileName)
i = cmd.ExecuteNonQuery()

The name of subjects sat and family members are temporarily stored in list-boxes to be inserted in the table cell as one data. But I don't know how to store the whole listbox data in the table cell. Any ideas. Appreciate in advance.

do you want to save all values of listbox ? – hdkhardik Jun 23 '15 at 12:00 — hdkhardik, Jun 23 '15 at 12:00

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

I think you are asking how to convert every item in a list box to a single string? Like this:

Dim itemList() As String, listMax As Integer, result As String, delim As String = "||"
'Set delim to whatever you want in between each listbox item
listMax = ListBox1.Items.Count - 1
If listMax >= 0 Then
    ReDim itemList(listMax)
    For i = 0 To listMax
        itemList(i) = ListBox1.Items(i).ToString
    Next
    result = Join(itemList, delim)
End If

Also, consider this the obligatory warning about SQL injection. I see you know how to paramaterize but are nevertheless concatenating user-entered strings directly into your command. This answer explains why this is a bad idea.

Thank you Josh, it has helped me a lot. Actually, you were in my mind. — Condrad Mariki, Jul 01 '15 at 10:31

How do I insert listbox data as one bulk data in a table cell in sql-server

1 Answers1