"No value given for one or more required parameters" VB.Net GridView Update

Question

I'm having an Access DB and connecting to it through VB.NET.

First, I show a table records in GridView with the user having ability to update it. After the user updates, he will press a Button to take the updated data from the GridView and update the Access database.

But while doing that I get the error No value given for one or more required parameters. What I'm missing here?

Code:

 Dim sConnectionString As String _
       = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\DB.accdb;Persist Security Info=False;"

Dim myConnection As OleDbConnection
Dim myAdapter As OleDbDataAdapter
Dim myTable As DataTable

Private Sub form1_Shown(sender As Object, e As EventArgs) Handles MyBase.Shown
        myConnection = New OleDbConnection(sConnectionString)
        Dim myCommand As New OleDbCommand("SELECT ID,Test FROM T1", myConnection)
        myConnection.Open()
        myAdapter = New OleDbDataAdapter(myCommand)
        myTable = New DataTable()
        myAdapter.Fill(myTable)
        setDataGridDataSource(myTable)
        setLabelTxt("Row Count: " + myTable.Rows.Count.ToString(), RowCount)
        DataGridView1.AllowUserToAddRows = True
        DataGridView1.AllowUserToDeleteRows = True
        DataGridView1.EditMode = DataGridViewEditMode.EditOnEnter
        myConnection.Close()
    End Sub

Private Sub button1_Click(sender As Object, e As EventArgs) Handles button1.Click
    Me.Validate()
    Dim command As OleDbCommand = New OleDbCommand("UPDATE T1 SET ID = ? , Test = ? WHERE ID = ?;", myConnection)
    myAdapter.UpdateCommand = command
    myAdapter.Update(myTable)
    myTable.AcceptChanges()
End Sub

Where do you assign your parameters with values in that OleDbCommand? As it stands right now you don't provide any values to the parameters in the SQL Query. — JaggenSWE, Sep 09 '15 at 08:06
@JaggenSWE because I'm using GridView, so the change will happen in the GridView by the user, I don't know how to put the parameters from GridView to the command. Where I will get the updated fields in the code? — Hesham Saeed, Sep 09 '15 at 10:54
Have a look at this article, it explains the concept quite nicely. It's in c# but the same logic applies to VB.NET and the core of the logic looks more or less identical across languages. http://www.codeproject.com/Articles/12846/Auto-Saving-DataGridView-Rows-to-a-SQL-Server-Data — JaggenSWE, Sep 09 '15 at 11:10

score 0 · Answer 1 · edited May 23 '17 at 10:26

0

Your sql command with text "UPDATE T1 SET ID = ? , Test = ? WHERE ID = ?;" expects 3 parameters, but your are not setting them anywhere in the code. See this example and assign parameters to your command as in:

cmd.Parameters.AddWithValue("ID", txtID.Text);
cmd.Parameters.AddWithValue("Test", txtTest.Text);
... and so on

edited May 23 '17 at 10:26

Community

1
1

answered Sep 09 '15 at 08:29

chrisl08

1,658
1
15
24

@chrisI08 I don't use Input Fields, I'm using GridView, how I can get the updated field from the GridView? – Hesham Saeed Sep 09 '15 at 10:55
To get value from gridview cell, see http://stackoverflow.com/questions/15512832/getting-value-from-a-gridview-cell – chrisl08 Sep 09 '15 at 12:07

score 0 · Accepted Answer · answered Sep 10 '15 at 05:34

The code should be edited to this:

Private Sub button1_Click(sender As Object, e As EventArgs) Handles button1.Click
        Me.Validate()
        Dim cmdBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(myAdapter)
        Dim changes As DataTable = myTable.GetChanges
        If changes IsNot Nothing Then
            myAdapter.Update(myTable)
        End If
        myTable.AcceptChanges()
End Sub

"No value given for one or more required parameters" VB.Net GridView Update

2 Answers2