Results 1 to 6 of 6

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

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    2

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

    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 list box 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 btnUpdateTable an 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 is 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:

    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)
                cmd.ExecuteNonQuery()
    
                cm.Parameters.Add(New OleDbParameter("@NewData", OleDbType.VarChar, 255, NewData))
                cm.Parameters.Add(New OleDbParameter("@recID", OleDbType.VarChar, 255, recID))
    
    
                cm.Parameters("@NewData").Value = NewData
                cm.Parameters("@recID").Value = recID
    
    
    
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Critical)
            End Try
    
        End Sub
    End Class

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

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

    I believe that this issue has been resolved courtesy of an answer I provided on another site but, curiously, you have posted slightly different code here. You still appear to have two different commands and you're adding the parameters to the wrong one but, in this case, you are executing the command before adding the parameters too, which would still be an issue even if you only had one command variable.
    Code:
                Dim con As New OleDbConnection(connString)
                con.Open()
                Dim cmd As New OleDbCommand("UPDATE [" & tblName & "] SET [" & colName & "] = @NewData WHERE ID = @recID", con)
                cmd.ExecuteNonQuery()
    
                cm.Parameters.Add(New OleDbParameter("@NewData", OleDbType.VarChar, 255, NewData))
                cm.Parameters.Add(New OleDbParameter("@recID", OleDbType.VarChar, 255, recID))
    
    
                cm.Parameters("@NewData").Value = NewData
                cm.Parameters("@recID").Value = recID
    I still don't see where 'cm' is declared in there though, so I'm still not sure how that would have compiled.

  3. #3
    Addicted Member
    Join Date
    Jul 2017
    Location
    Exeter, UK
    Posts
    180

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

    At first glance in your btnUpdateTable event you are calling cmd.ExecuteNonQuery() BEFORE you set the parameters.

  4. #4

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    2

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

    Quote Originally Posted by jmcilhinney View Post
    I believe that this issue has been resolved courtesy of an answer I provided on another site but, curiously, you have posted slightly different code here. You still appear to have two different commands and you're adding the parameters to the wrong one but, in this case, you are executing the command before adding the parameters too, which would still be an issue even if you only had one command variable.
    Code:
                Dim con As New OleDbConnection(connString)
                con.Open()
                Dim cmd As New OleDbCommand("UPDATE [" & tblName & "] SET [" & colName & "] = @NewData WHERE ID = @recID", con)
                cmd.ExecuteNonQuery()
    
                cm.Parameters.Add(New OleDbParameter("@NewData", OleDbType.VarChar, 255, NewData))
                cm.Parameters.Add(New OleDbParameter("@recID", OleDbType.VarChar, 255, recID))
    
    
                cm.Parameters("@NewData").Value = NewData
                cm.Parameters("@recID").Value = recID
    I still don't see where 'cm' is declared in there though, so I'm still not sure how that would have compiled.
    Yeah I don’t understand how it didn’t stop the program from compiling or having a red underline when it should’ve. Very strange indeed. But hey it’s fixed and works no so I’m very happy hahah.

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

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

    It may be too late now but you can right-click on a variable or other identifier and select Go To Declaration to go straight to where it is declared in code. I think that's right. I use ReSharper so I'm often not sure what's VS and what's ReSharper but I think they both provide something similar.

  6. #6
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

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

    They do, and you really SHOULD do that. When you encounter something in code that is working when you think it shouldn't, you should consider that to be about as bad as it not working. The only way this will work is if cm IS declared somewhere. This means that changing something else could break this code because you aren't aware of the entanglement between the two.
    My usual boring signature: Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width