How to insert data from gridview with checkbox selected?

Question

0.00/5 (No votes)

See more:

Hi
I have problem to insert data from Gridview checked item
If i tick multiple line, first ticked data only inserted into table.

Pls help me

What I have tried:

I have tried this

SQL

cmd = New SqlCommand
cmd.Connection = conn
For Each gvrow As GridViewRow In gvSubjects.Rows
    Dim chkSelect As CheckBox = DirectCast(gvrow.FindControl("chkSelect"), CheckBox)
    If chkSelect.Checked Then

        cmd.CommandText = "insert into ADM_Faculty_Subject (Code, Name, Classes, SubName, Charges, Location) values ('" + Me.txtCode.Text + _
                                  "','" + Me.txtName.Text + _
                                  "','" + gvrow.Cells(2).Text + _
                                  "','" + gvrow.Cells(3).Text + _
                                  "','" + gvrow.Cells(4).Text + _
                                  "','" + Me.cboLocation.SelectedValue + "')"

        cmd.CommandType = CommandType.Text
        conn.Open()
        cmd.ExecuteNonQuery()
        cmd = Nothing
        conn.Close()
    End If
Next

Posted 2-Aug-16 0:51am

Maideen Abdul Kader

Updated 2-Aug-16 5:25am

Add a Solution

Comments

Richard Deeming 2-Aug-16 11:16am

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Accepted Answer · 2016-08-02T05:25:00

Start by fixing the SQL Injection[^] vulnerability in your code.

Then, you need to remove the line that's setting the cmd object to Nothing for each iteration.

You'll probably also want to wrap the entire block in a transaction, to prevent partial inserts.

VB.NET

Using conn As New SqlConnection("YOUR CONNECTION STRING HERE")
    conn.Open()
    
    Using transaction As SqlTransaction = conn.BeginTransaction()
        Using cmd As New SqlCommand("insert into ADM_Faculty_Subject (Code, Name, Classes, SubName, Charges, Location) values (@Code, @Name, @Classes, @SubName, @Charges, @Location)", conn, transaction)
            
            cmd.CommandType = CommandType.Text
            
            ' TODO: Correct data types and sizes for the parameters to match the columns:
            Dim pCode As SqlParameter = cmd.Parameters.Add("@Code", SqlDbType.VarChar, 50)
            Dim pName As SqlParameter = cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50)
            Dim pClasses As SqlParameter = cmd.Parameters.Add("@Classes", SqlDbType.VarChar, 50)
            Dim pSubName As SqlParameter = cmd.Parameters.Add("@SubName", SqlDbType.VarChar, 50)
            Dim pCharges As SqlParameter = cmd.Parameters.Add("@Charges", SqlDbType.VarChar, 50)
            Dim pLocation As SqlParameter = cmd.Parameters.Add("@Location", SqlDbType.VarChar, 50)
            
            pCode.Value = Me.txtCode.Text
            pName.Value = Me.txtName.Text
            pLocation.Value = Me.cboLocation.SelectedValue
            
            For Each gvrow As GridViewRow In gvSubjects.Rows
                Dim chkSelect As CheckBox = DirectCast(gvrow.FindControl("chkSelect"), CheckBox)
                If chkSelect.Checked Then
                    pClasses.Value = gvrow.Cells(2).Text
                    pSubName.Value = gvrow.Cells(3).Text
                    pCharges.Value = gvrow.Cells(4).Text
                    cmd.ExecuteNonQuery()
                End If
            Next
        End Using
        
        transaction.Commit()
    End Using
End Using