VB.NET Insert DataGridVIew content into SQL Server table

user_16652610 0
Viewed 276

I'm having issues inserting data from my datagridview into my SQL Server table

The error I get is this:

System.Data.SqlClient.SqlException: The parameterized query '(@Articulo varchar(8000),@Cantidad varchar(8000),@Precio varchar' expects the parameter '@Articulo', which was not supplied.

My code:

Private Sub btnGetTotal_Click(sender As Object, e As EventArgs) Handles btnGetTotal.Click
    Dim connection As SqlConnection = New SqlConnection("xxx")
    Dim command As SqlCommand = New SqlCommand("INSERT INTO Ordenes_Full (Articulo, Cantidad, Precio) VALUES (@Articulo, @Cantidad, @Precio)")
    Dim dataAdapter As New Data.SqlClient.SqlDataAdapter
    Dim dataSet As New Data.DataSet

    command.Parameters.Add("@Articulo", SqlDbType.VarChar)
    command.Parameters.Add("@Cantidad", SqlDbType.VarChar)
    command.Parameters.Add("@Precio", SqlDbType.VarChar)

    connection.Open()
    command.Connection = connection

    For i As Integer = 0 To DataGridView1.Rows.Count - 1
        command.Parameters(0).Value = DataGridView1.Rows(i).Cells(0).Value
        command.Parameters(1).Value = DataGridView1.Rows(i).Cells(1).Value
        command.Parameters(2).Value = DataGridView1.Rows(i).Cells(2).Value
        command.ExecuteNonQuery()
    Next
 
End Sub
vb.net sql-server sql
1 Answers

I would suggest doing this quite differently, as I suggested in a comment on the question. If you're going to stick with this method though, you have to make sure that "empty" cells are saved to the database as NULL. You can do that like this:

command.Parameters(0).Value = If(DataGridView1.Rows(i).Cells(0).Value, DBNull.Value)

That will use the Value of the cell unless it is Nothing, in which case it will use DBNull.Value instead.

DBNull.Value is what ADO.NET uses to represent a database NULL. The reason that it cannot just use Nothing for that is because ADO.NET was created in the days before nullable value types. If you used Nothing where, for instance, an Integer was expected then that would be interpreted as zero rather than NULL.

user_5841830
Related