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.
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
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