Question What does no value given for one or more required parameters mean? How to fix?

[DivineBlaze33]

Hi all, I'm making a program in which the user can update a table of their choice from a list box. When an item in the listbox is selected, it shows the MS-Access database in the DataGridView. When a user selects a cell in the DataGridView, it will show the ID, the field name and the actual data inside the selected cell in textboxes, so the user can change the table. Once they click btnUpdateTablean UPDATE query will be run to change the cell data based on the ID and the field name.However, when clicking btnUpdateTable a message box appears reading:
"No value given for one or more required parameters"
I don't know what the problem is and how to fix it, I don't understand what parameter is null. Thanks all, appreciate it. Below is the code:

Imports System.Data.OleDb
Public Class frmViewTables
    Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "\SAC1 Database.mdb"
    Dim MyConn As New OleDbConnection
    Dim da As OleDbDataAdapter
    Dim ds As DataSet
    Dim tables As DataTableCollection
    Dim source1 As New BindingSource
    Dim dt As DataTable
    Dim SelectedTable As String


    Private Sub lbxTables_SelectedIndexChanged(sender As Object, e As EventArgs) Handles lbxTables.SelectedIndexChanged


        If lbxTables.SelectedItem = "tblOrders" And frmLogin.AdminDetails = True Then
            SelectedTable = "tblOrders"
            Dim source1 As New BindingSource
            Dim ds = New DataSet
            Dim tables = ds.Tables
            Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "\SAC1 Database.mdb")
            Dim da As OleDbDataAdapter = New OleDbDataAdapter()
            Dim cmd As New OleDbCommand("Select * from [tblOrders]", cn)
            da.SelectCommand = cmd
            da.Fill(ds, "tblOrders")
            Dim view As New DataView(tables(0))
            source1.DataSource = view
            dgvDynamic.DataSource = view
        ElseIf lbxTables.SelectedItem = "tblOrders" Then
            SelectedTable = "tblOrders"
            Dim source1 As New BindingSource
            Dim ds = New DataSet
            Dim tables = ds.Tables
            Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "\SAC1 Database.mdb")
            Dim da As OleDbDataAdapter = New OleDbDataAdapter()
            Dim cmd As New OleDbCommand("Select * from [tblOrders] where Username = @username", cn)
            cmd.Parameters.Add("@username", OleDbType.VarChar, 255).Value = frmLogin.SuccessfulLoginUsername
            da.SelectCommand = cmd
            da.Fill(ds, "tblOrders")
            Dim view As New DataView(tables(0))
            source1.DataSource = view
            dgvDynamic.DataSource = view
        End If


        If lbxTables.SelectedItem = "tblTickets" And frmLogin.AdminDetails = True Then
            SelectedTable = "tblTickets"
            Dim source1 As New BindingSource
            Dim ds = New DataSet
            Dim tables = ds.Tables
            Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "\SAC1 Database.mdb")
            Dim da As OleDbDataAdapter = New OleDbDataAdapter()
            Dim cmd As New OleDbCommand("Select * from [tblTickets]", cn)
            da.SelectCommand = cmd
            da.Fill(ds, "tblTickets")
            Dim view As New DataView(tables(0))
            source1.DataSource = view
            dgvDynamic.DataSource = view
        ElseIf lbxTables.SelectedItem = "tblTickets" Then
            SelectedTable = "tblTickets"
            Dim source1 As New BindingSource
            Dim ds = New DataSet
            Dim tables = ds.Tables
            Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "\SAC1 Database.mdb")
            Dim da As OleDbDataAdapter = New OleDbDataAdapter()
            Dim cmd As New OleDbCommand("Select * from [tblTickets] where Username = @username", cn)
            cmd.Parameters.Add("@username", OleDbType.VarChar, 255).Value = frmLogin.SuccessfulLoginUsername
            da.SelectCommand = cmd
            da.Fill(ds, "tblTickets")
            Dim view As New DataView(tables(0))
            source1.DataSource = view
            dgvDynamic.DataSource = view
        End If


        If lbxTables.SelectedItem = "tblUsers" And frmLogin.AdminDetails = True Then
            SelectedTable = "tblUsers"
            Dim source1 As New BindingSource
            Dim ds = New DataSet
            Dim tables = ds.Tables
            Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "\SAC1 Database.mdb")
            Dim da As OleDbDataAdapter = New OleDbDataAdapter()
            Dim cmd As New OleDbCommand("Select * from [tblUsers]", cn)
            da.SelectCommand = cmd
            da.Fill(ds, "tblTickets")
            Dim view As New DataView(tables(0))
            source1.DataSource = view
            dgvDynamic.DataSource = view
        End If
    End Sub


    Private Sub frmViewTables_Shown(sender As Object, e As EventArgs) Handles Me.Shown


        If frmLogin.AdminDetails = True Then
            lbxTables.Items.Add("tblUsers")
            lbxTables.Items.Add("tblOrders")
            lbxTables.Items.Add("tblTickets")
            dgvDynamic.ReadOnly = False
        Else
            lbxTables.Items.Add("tblOrders")
            lbxTables.Items.Add("tblTickets")
        End If


    End Sub


    Private Sub dgvDynamic_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgvDynamic.CellClick


        txtID.Text = dgvDynamic.CurrentRow.Cells(0).Value.ToString()
        txtSelectedCellData.Text = dgvDynamic.CurrentCell.Value.ToString()
        For Each c As DataGridViewCell In dgvDynamic.SelectedCells
            txtColumnName.Text = (dgvDynamic.Columns(c.ColumnIndex).HeaderText)
        Next


    End Sub


    Private Sub GetRecordID()
        Dim rowContent As String = String.Empty


        dgvDynamic.CurrentRow.Cells(0).Value.ToString()
        MessageBox.Show(dgvDynamic.CurrentRow.Cells(0).Value.ToString())


    End Sub


    Private Sub btnGetID_Click(sender As Object, e As EventArgs) Handles btnGetID.Click
        GetRecordID()
    End Sub


    Private Sub GetColumnName()
        For Each c As DataGridViewCell In dgvDynamic.SelectedCells
            MessageBox.Show(dgvDynamic.Columns(c.ColumnIndex).HeaderText)
        Next
    End Sub


    Private Sub btnGetColumnName_Click(sender As Object, e As EventArgs) Handles btnGetColumnName.Click
        GetColumnName()
    End Sub


    Private Sub btnUpdateTable_Click(sender As Object, e As EventArgs) Handles btnUpdateTable.Click
        Dim tblName As String = SelectedTable
        Dim colName As String = txtColumnName.Text
        Dim recID As String = txtID.Text
        Dim NewData As String = txtSelectedCellData.Text


        Try
            Dim con As New OleDbConnection(connString)
            con.Open()
            Dim cmd As New OleDbCommand("UPDATE [" & tblName & "] SET [" & colName & "] = @NewData WHERE ID = @recID", con)




            cm.Parameters.Add(New OleDbParameter("@NewData", OleDbType.VarChar, 255))
            cm.Parameters.Add(New OleDbParameter("@recID", OleDbType.VarChar, 255))




            cm.Parameters("@NewData").Value = NewData
            cm.Parameters("@recID").Value = recID


            cmd.ExecuteNonQuery()




        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical)
        End Try


    End Sub
End Class

 

[jmcilhinney]

Firstly, please don't post all your code an expect us to trawl through it to find the relevant part.

As for the issue, it's not completely clear. That's because I can see what looks to be the issue but, if it is, then that code should not even compile. Look at this:

            Dim con As New OleDbConnection(connString)
            con.Open()
            Dim [COLOR="#0000FF"]cmd[/COLOR] As New OleDbCommand("UPDATE [" & tblName & "] SET [" & colName & "] = @NewData WHERE ID = @recID", con)




            [COLOR="#FF0000"]cm[/COLOR].Parameters.Add(New OleDbParameter("@NewData", OleDbType.VarChar, 255))
            [COLOR="#FF0000"]cm[/COLOR].Parameters.Add(New OleDbParameter("@recID", OleDbType.VarChar, 255))




            [COLOR="#FF0000"]cm[/COLOR].Parameters("@NewData").Value = NewData
            [COLOR="#FF0000"]cm[/COLOR].Parameters("@recID").Value = recID


            [COLOR="#0000FF"]cmd[/COLOR].ExecuteNonQuery()

According to that code, you create a command object, add parameters to a different command object, then execute the one you created. That would explain why you get an error message about parameters not being set: you never add them to the command you execute. The thing is though, I can't see anywhere in that code that you are actually declaring that 'cm' variable, so I'm not sure how that code compiles. Perhaps you have declared it in a module or something, but why would you do that?

 

[jmcilhinney]

By the way, while the way you're adding parameters is not inherently wrong, it's more verbose that is required. You don't need to invoke an OleDbParameter constructor because the Add method will take the same arguments. Also, the Add method returns the parameter object so you can set the Value directly. That means that this:

cm.Parameters.Add(New OleDbParameter("@NewData", OleDbType.VarChar, 255))
cm.Parameters.Add(New OleDbParameter("@recID", OleDbType.VarChar, 255))

cm.Parameters("@NewData").Value = NewData
cm.Parameters("@recID").Value = recID

can be simplified to this:

cm.Parameters.Add("@NewData", OleDbType.VarChar, 255).Value = NewData
cm.Parameters.Add("@recID", OleDbType.VarChar, 255).Value = recID

 

[DivineBlaze33]

By the way, while the way you're adding parameters is not inherently wrong, it's more verbose that is required. You don't need to invoke an OleDbParameter constructor because the Add method will take the same arguments. Also, the Add method returns the parameter object so you can set the Value directly. That means that this:

cm.Parameters.Add(New OleDbParameter("@NewData", OleDbType.VarChar, 255))
cm.Parameters.Add(New OleDbParameter("@recID", OleDbType.VarChar, 255))

cm.Parameters("@NewData").Value = NewData
cm.Parameters("@recID").Value = recID

can be simplified to this:

cm.Parameters.Add("@NewData", OleDbType.VarChar, 255).Value = NewData
cm.Parameters.Add("@recID", OleDbType.VarChar, 255).Value = recID

Thank you very much, you resolved the issue!! I'm sorry for posting so much code, just thought you would like the context of it all but that obviously wasn't needed. Thank you so much, you don't understand how long I've been stuck on this. I just need to create a message box that will say the record has been successfully updated and then I'm good to go. Thanks, heaps jmcilhinney.

 

[jmcilhinney]

Also, is the ID column really text? That's possible but a bit odd. Numeric identifiers are more command and a column named ID would usually be numeric. If that column contains some text that is actually part of the record rather than just an identifier then that name doesn't seem appropriate.

 

[jmcilhinney]

I'm sorry for posting so much code, just thought you would like the context of it all but that obviously wasn't needed.

It's not always easy to know what's relevant and what's not but posting a lot of code actually makes it more likely that people will miss the issue or just not bother looking. I'd suggest that you start with the method in which the issue occurs and then add more if and only if you have a specific reason to do so. We can always ask for more if we need it.