Click here to Skip to main content
14,732,809 members
Please Sign up or sign in to vote.
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
Updated 19-Aug-20 22:33pm
Comments
MadMyche 21-Apr-20 7:28am
   
Are you trying to insert multiple roles; 1 row for each Checkbox value?

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]
   
v2
Comments
Maciej Los 21-Apr-20 2:38am
   
"I can't even remember whether VB array indices start at 0 or 1"
In .Net - starts from zero (Option Base switch has been deleted)!
In VB, VBA - depeneds on switch Option Base 0|1
Garth J Lancaster 21-Apr-20 2:39am
   
cheers Maciej - I could have looked it up, but didn't want to do all the poster's work
Maciej Los 21-Apr-20 2:44am
   
Hi, Garth!
I hurried, sorry ;)
BTW: +5!
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
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
INSERT into Table
SELECT @T1, @T2, @T3, @T4, value
FROM   STRING_SPLIT(@CB1, ',')
Reference: MS Docs: STRING_SPLIT (Transact-SQL)[^]
   
v4
Comments
MadMyche 22-Apr-20 16:11pm
   
What is being inserted into the 5th(?) column for the checkbox value?

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900