ASP. NET (VB) - how to insert multiple checkboxlist values to SQL server?

Question

0.00/5 (No votes)

See more:

I created a form in ASP. Net (VB) with 4 single line textbook and one checkbox list.
With the code I tried only one value is inserting into the database. I want multiple values to be inserted. I am really not sure how to get multiple selected checkbox, inserted into the database. Please need help, I would appreciate it if I can see an example.
Thank you

What I have tried:

Con. ConnectionString="DataSouce=.... Security=True"
Con. Open()
Cmd= new SqlCommand("Insert into table values(' " & Text1.Text & " ',' " & Text2.Text &" ', ' " & Text3.Text & " ', ' " & Text4.Text & " ',, ' " & CheckBoxList. Text & " ') ", con)
Cmd. ExecuteNonQuery()
Con. Close()

Posted 20-Apr-20 15:12pm

Newcodervb

Updated 19-Aug-20 21:33pm

Add a Solution

Comments

MadMyche 21-Apr-20 7:28am

Are you trying to insert multiple roles; 1 row for each Checkbox value?

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Garth J Lancaster · Accepted Answer · 2020-04-20T16:08:00

I would have thought that you'd need to create an array out of your (non-null) TextBox values, loop though that, and run the insert sql, but that's a big assumption given you haven't put your table declaration (is it a single column, multiple ... ?)

btw, please don't build sql like that - look up parameterized sql query eg

Dim textValues(4) as String
' Populate textValues with non-null Text[n].Text values giving number of elements in numText

Dim sql As String = "Insert into table values(@textBoxValue)"

For ctr As Integer = 0 To numText-1
    Using Con As New SqlConnection("DataSource=.... Security=True"), _
        cmd As New SqlCommand(sql, Con)

        cmd.Parameters.Add("@textBoxValue", SqlDbType.VarChar, 50).Value = textValues(ctr)
        cmd.ExecuteNonQuery()
    End Using
Next

That's pretty inefficient, in that it creates and disposes of the connection numText times, but show's the idea at least

disclaimer : I haven't written VB since I had hair, think 30+ years, so get 'the idea' from what I've put, not the exact code - you could probably re-arrange the using/for loop to get

Using Con As New SqlConnection("DataSource=.... Security=True"), _
        cmd As New SqlCommand(sql, Con)

    For ctr As Integer = 0 To numText-1
        cmd.Parameters.Add("@textBoxValue", SqlDbType.VarChar, 50).Value = textValues(ctr)
        cmd.ExecuteNonQuery()
        cmd.Parameters.Clear() ' Assumption - this is a valid method/call 
    Next 
End Using

or similar

[edit] the first part of the problem might start like this

' Loop through ALL textboxes on form adding non-null values to textValues Array and increasing numText Count 
Dim ctrl As Control
numText = 0
For Each ctrl In Me.Controls
   If (ctrl.GetType.ToString = "System.Windows.Forms.TextBox") Then
        String.IsNullOrEmpty(strTest) Then 
            textValues(numText) = CType(ctrl, TextBox).Text
            numText += 1
        End If
   End If
Next

But again, that's the idea - I can't even remember whether VB array indices start at 0 or 1 !

[/Edit]

MadMyche · Accepted Answer · 2020-04-21T01:17:00

Have you ever heard of SQL Injection, which has been one of the top 10 application vulnerabilities for over 20 years? Your code is susceptible to it; you should NEVER EVER create a query by concatenating SQL commands with user input.

The proper way to put user input into an SQL Query is to use Parameters[^]. In the .NET Framework it is a collection that is one of the properties of the command element.
Besides eliminating the security risks, it also takes care of the data-types so that you will not need to wrap text in single quotes etc. The code also looks a lot cleaner.

This is what your code could look like when implementing this

VB

Con.ConnectionString="DataSouce=.... Security=True"
Con.Open()

Cmd= new SqlCommand("Insert into table values (@T1, @T2, @T3, @T4, @CB1) ", con)
Cmd.Parameters.AddWithValue("@T1", Text1.Text)
Cmd.Parameters.AddWithValue("@T2", Text2.Text)
Cmd.Parameters.AddWithValue("@T3", Text3.Text)
Cmd.Parameters.AddWithValue("@T4", Text4.Text)
Cmd.Parameters.AddWithValue("@CB1", CheckBoxList.Text)

Cmd.ExecuteNonQuery()
Con.Close()

Reference: MS Docs : SqlParameterCollection.AddWithValue(String, Object) Method[^]

Now onto your issue.... You really weren't clear in the question and did not provide sample input and desired results, so I can only assume that you want to have one row entered for each checkbox that is checked.

If you are using a newer version of SQL Server, there is a table function called STRING_SPLIT()[^] which will return a table, splitting delineated values into rows
If your CheckBox is returning a comma-delineated list, you can simple replace your INSERT command with this line of T-SQL

SQL

INSERT into Table
SELECT @T1, @T2, @T3, @T4, value
FROM   STRING_SPLIT(@CB1, ',')

Reference: MS Docs: STRING_SPLIT (Transact-SQL)[^]

ASP. NET (VB) - how to insert multiple checkboxlist values to SQL server?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

ASP. NET (VB) - how to insert multiple checkboxlist values to SQL server?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us