CODEWITHSUNDEEP
vb.net
mhmoud alfhel
mhmoud alfhel

Reputation: 1

Failed to convert parameter value from a string to int32

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()

Upvotes: 0

Views: 399

Answers (1)

Andrew Morton
Andrew Morton

Reputation: 25037

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

Upvotes: 2

Related Questions