CODEWITHSUNDEEP
sqlvb.net
Jenny
Jenny

Reputation: 27

I try to add data from data grid view to sql database but getting error 'The parameterized query which was not supplied.' vb.net

I want to add data from data grid view to sql database,the data saved to database but keep getting this error

System.Data.SqlClient.SqlException: 'The parameterized query '(@KodeBuku nvarchar(4000),@JudulBuku nvarchar(4000),@PenerbitBuk' expects the parameter '@KodeBuku', which was not supplied.'

My code

For Each row As DataGridViewRow In dgv_buku.Rows
        Dim constring As String = "data source=MSI;initial catalog=DB_PERPUS;integrated security=true"
        Using conn As New SqlConnection(constring)
            Using cmd As New SqlCommand("Insert into Tbl_Buku values(@KodeBuku,@JudulBuku,@PengarangBuku,@PenerbitBuku,@TahunBuku,@StokBuku)", conn)
                cmd.Parameters.AddWithValue("@KodeBuku", row.Cells("kode").Value)
                cmd.Parameters.AddWithValue("@JudulBuku", row.Cells("judul").Value)
                cmd.Parameters.AddWithValue("@PenerbitBuku", row.Cells("penerbit").Value)
                cmd.Parameters.AddWithValue("@PengarangBuku", row.Cells("pengarang").Value)
                cmd.Parameters.AddWithValue("@StokBuku", row.Cells("stok").Value)
                cmd.Parameters.AddWithValue("@TahunBuku", row.Cells("tahun").Value)
                conn.Open()
                cmd.ExecuteNonQuery()
                conn.Close()
            End Using
        End Using
    Next
    MsgBox("Save")

Upvotes: 0

Views: 151

Answers (1)

John
John

Reputation: 3182

That indicates that the Value of the cell in that column for at least one row was Nothing, which won't work with ADO.NET. If you want to insert NULL then you have to use DBNull.Value. The way you're doing things, that means explicitly checking for Nothing. You also should not be using AddWithValue, but rather calling Add and specifying the data type and, optionally, the size, e.g.

cmd.Parameters.Add("@KodeBuku", SqlDbType.VarChar, 50).Value = 
    If(row.Cells("kode").Value, DBNull.Value)

I'm not 100% sure without testing but you may have to use CObj(DBNull.Value). Note that that If operator will return the first argument if it's not Nothing and the second if it is.

A better option would be to not use a loop to call ExecuteNonQuery in the first place. You should create a DataTable with the appropriate schema and bind that to the grid. You can then use a data adapter with an appropriate InsertCommand to save all the data with a single call to Update. Every field in a DataTable contains DBNull.Value by default, so there's no need to do any check for Nothing.

If you do want to go the DataTable option, you can do like this:

Private ReadOnly connection As New SqlConnection("connection string here")
Private ReadOnly adapter As New SqlDataAdapter("SELECT * FROM Table1", connection)
Private ReadOnly table As New DataTable

and this:

Dim command As New SqlCommand("INSERT INTO Table1 (Column1, Column2) VALUES (@Column1, @Column2)", connection)

With command.Parameters
    .Add("@Column1", SqlDbType.VarChar, 50, "Column1")
    .Add("@Column2", SqlDbType.Int, 0, "Column2")
End With

adapter.InsertCommand = command
adapter.FillSchema(table, SchemaType.Source)

BindingSource1.DataSource = table
DataGridView1.DataSource = BindingSource1

and this:

BindingSource1.EndEdit()
adapter.Update(table)

You can add the columns to the table manually if you want, instead of calling FillSchema.

Upvotes: 2

Related Questions