Error when trying to insert data from vb.net datagridview into SQL Server table

Question

I get this error when I run the code. The records are inserted into the table, but the program stops at the error.

The parameterized query '(@EmpName varchar(8000),@USDBasic varchar(8000),@OtherUSDEarning' expects the parameter '@EmpName', which was not supplied.

Code:

 Dim connetionString As String
 Dim cnn As SqlConnection

 connetionString = "Data Source=Server\SQlExpress;Initial Catalog=CreSolDemo;User ID=sa;Password=Mkwn@011255"
 cnn = New SqlConnection(connetionString)
       
 If DataGridView1.Rows.Count > 0 Then
     Dim cmd As New Data.SqlClient.SqlCommand
     cmd.CommandText = " INSERT INTO TempPeriodTrans (EmpName, USDBasic, OtherUSDEarnings, ZDollarBasic, OtherZDEarnings) VALUES  (@EmpName, @USDBasic, @otherUSDEarnings, @ZDollarBasic, @OtherZDEarnings) "

     cmd.Parameters.Add("@EmpName", SqlDbType.VarChar)
     cmd.Parameters.Add("@USDBasic", SqlDbType.VarChar)
     cmd.Parameters.Add("@OtherUSDEarnings", SqlDbType.VarChar)
     cmd.Parameters.Add("@ZDollarBasic", SqlDbType.VarChar)
     cmd.Parameters.Add("@OtherZDEarnings", SqlDbType.VarChar)

     cmd.Connection = cnn

     cnn.Open()

     For i As Integer = 0 To DataGridView1.Rows.Count - 1
         cmd.Parameters(0).Value = DataGridView1.Rows(i).Cells(0).Value
         cmd.Parameters(1).Value = DataGridView1.Rows(i).Cells(1).Value
         cmd.Parameters(2).Value = DataGridView1.Rows(i).Cells(2).Value
         cmd.Parameters(3).Value = DataGridView1.Rows(i).Cells(3).Value
         cmd.Parameters(4).Value = DataGridView1.Rows(i).Cells(4).Value

         cmd.ExecuteNonQuery()
      Next

      cnn.Close()
   End If

   MsgBox("Record saved")

End Sub

Answer 1

There seem to be a few things here.

1. As a rule, any time you open a connection to your data base it should be wrapped in a Using block so that that connection gets closed and disposed before you exit that block
2. You have a lot of params that are being set as SqlDbType.varchar where you should probably have other types. (SqlDbType.Money in particular)
3. When you are working with sqlcommand, it is worth wrapping it in a using block as well and creating a new one as you need it.

There is some memory to it where it will try not to reuse parameters in subsequent queries. Instead of just changing the value of the param, throw that sqlCommand in the trash bin each time and grab a new one. This is where I believe your problem is. I moved the sqlcommand creation into your loop and declare the values in-line below.

Also, protip, avoid including your actual password in the connetion string on Stack Overflow

    Dim connectionString As String = "yourConnectionString"
        Using cnn As new SqlConnection(connectionString)
            cnn.Open()
            For Each row in DataGridView1.Rows
                Using cmd As New Data.SqlClient.SqlCommand("INSERT INTO TempPeriodTrans (EmpName, USDBasic, OtherUSDEarnings, ZDollarBasic, OtherZDEarnings) Values (@EmpName, @USDBasic, @otherUSDEarnings, @ZDollarBasic, @OtherZDEarnings) ", cnn)
                    cmd.Parameters.Add("@EmpName", SqlDbType.VarChar).value = row.Cells(0).Value
                    cmd.Parameters.Add("@USDBasic", SqlDbType.VarChar).value = row.Cells(1).Value
                    cmd.Parameters.Add("@OtherUSDEarnings", SqlDbType.VarChar).value = row.Cells(2).Value
                    cmd.Parameters.Add("@ZDollarBasic", SqlDbType.VarChar).value = row.Cells(3).Value
                    cmd.Parameters.Add("@OtherZDEarnings", SqlDbType.VarChar).value = row.Cells(4).Value

                    cmd.ExecuteNonQuery()
                End using
            Next
        End using
        MsgBox("Record Saved")
    End Sub