Failed to convert parameter value from a string to int32

Question

cmd1.CommandText = "insert into tblInvoiceDetails (InvoiceNo)values(@InvoiceNo )"

With cmd1.Parameters
    .AddWithValue("@InvoiceNo", NumericUpDown1.Value)

     cmd1.CommandText = "insert into tblInvoiceDetails (itemno,ItemName,Qt,Price,Total)values(@itemno,@ItemName,@Qt,@Price,@Total)"
     'cmd1.Parameters.Add("@InvoiceNo", NumericUpDown1 .Value )
      cmd1.Parameters.Add("@itemno", SqlDbType.Int)
      cmd1.Parameters.Add("@ItemName", SqlDbType.VarChar)
      cmd1.Parameters.Add("@Qt", SqlDbType.Int)
      cmd1.Parameters.Add("@Price", SqlDbType.Money)
      cmd1.Parameters.Add("@Total", SqlDbType.Money)

      For x As Integer = 0 To DataGridView2.RowCount - 1
          cmd1.Parameters(0).Value = DataGridView2.Rows(x).Cells(0).Value
          cmd1.Parameters(1).Value = DataGridView2.Rows(x).Cells(1).Value
          cmd1.Parameters(2).Value = DataGridView2.Rows(x).Cells(2).Value
          cmd1.Parameters(3).Value = DataGridView2.Rows(x).Cells(3).Value
          cmd1.Parameters(4).Value = DataGridView2.Rows(x).Cells(4).Value
      Next
End With

cmd1.ExecuteNonQuery()
cmd1.Parameters.Clear()

Did you get any error messages? if so you should probably add it to your question. Also the question would probably be helped by some context, like I am trying to do X because Y, I expect my input A to look like output B but it looks like this instead. — user254694, Jan 17 '20 at 09:57
Although using SQL parameters is correct, please take note of [AddWithValue is Evil](https://www.dbdelta.com/addwithvalue-is-evil/), [AddWithValue is evil!](http://chrisrickard.blogspot.com/2007/06/addwithvalue-is-evil.html) and [Can we stop using AddWithValue() already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) — Andrew Morton, Jan 17 '20 at 10:01
`cmd1.Parameters(1).Value = DataGridView2.Rows(x).Cells(1).Value` should probably be `cmd1.Parameters(1).Value = Convert.ToInt32(DataGridView2.Rows(x).Cells(1).Value)`, similarly for the other `SqlDbType.Int` parameter, and use `Convert.ToDecimal` for the `SqlDbType.Money` ones. However, you may want to read [Should you choose the MONEY or DECIMAL(x,y) datatypes in SQL Server?](https://stackoverflow.com/q/582797/1115360) *"Never ever should you use money. It is not precise, and it is pure garbage; always use decimal/numeric."* — Andrew Morton, Jan 17 '20 at 10:06
And I think you may have your parameter indexes off by one, because parameter(0) will be the @InvoiceNo. — Andrew Morton, Jan 17 '20 at 10:13

Andrew Morton · Answer 1 · 2020-01-17T15:04:50.627

It looks like you are inserting several values from a DataGridView into the database, and they all have the same InvoiceNo.

To make it easier to see which parameter is which, you can refer to them by the parameter name.

To make sure that the data types are correct, use the various Convert functions.

You would need to execute the query for each row of data. There is no need to clear the parameters, just change the value of each one.

So your code would end up something like:

Dim sql = "INSERT INTO tblInvoiceDetails ([itemno], [ItemName], [Qt], [Price], [Total]) VALUES (@itemno, @ItemName, @Qt, @Price, @Total)"

Using conn As New SqlConnection("your connection string"),
        cmd1 As New SqlCommand(sql, conn)

    cmd1.Parameters.Add("@InvoiceNo", SqlDbType.Int)
    cmd1.Parameters.Add("@itemno", SqlDbType.Int)
    'TODO: Use the correct size for the VARCHAR column:
    cmd1.Parameters.Add("@ItemName", SqlDbType.VarChar, 128)
    cmd1.Parameters.Add("@Qt", SqlDbType.Int)
    cmd1.Parameters.Add("@Price", SqlDbType.Money)
    cmd1.Parameters.Add("@Total", SqlDbType.Money)

    cmd1.Parameters("@InvoiceNo").Value = Convert.ToInt32(NumericUpDown1.Value)

    conn.Open()

    For x As Integer = 0 To DataGridView2.RowCount - 1
        cmd1.Parameters("@itemno").Value = Convert.ToInt32(DataGridView2.Rows(x).Cells(0).Value)
        cmd1.Parameters("@ItemName").Value = Convert.ToString(DataGridView2.Rows(x).Cells(1).Value)
        cmd1.Parameters("@Qt").Value = Convert.ToInt32(DataGridView2.Rows(x).Cells(2).Value)
        cmd1.Parameters("@Price").Value = Convert.ToDecimal(DataGridView2.Rows(x).Cells(3).Value)
        cmd1.Parameters("@Total").Value = Convert.ToDecimal(DataGridView2.Rows(x).Cells(4).Value)

        cmd1.ExecuteNonQuery()

    Next

End Using

The Using Statement takes care of cleaning up unmanaged resources used by the SqlConnection and SqlCommand.

When you have a string parameter (@ItemName in this case), it is best to tell it the size of the column in the database. I guessed at 128, but you should put in the actual value.

Also, you really should give the controls meaningful names, for example "InvoiceNoSelector" instead of "NumericUpDown1".

Failed to convert parameter value from a string to int32

1 Answers1