-
Jan 18th, 2018, 11:14 PM
#1
Thread Starter
New Member
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
-
Jan 19th, 2018, 06:16 AM
#2
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.
-
Jan 19th, 2018, 06:16 AM
#3
Addicted Member
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.
-
Jan 19th, 2018, 06:48 AM
#4
Thread Starter
New Member
Re: What does no value given for one or more required parameters mean? How to fix?
Originally Posted by jmcilhinney
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.
-
Jan 19th, 2018, 08:16 AM
#5
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.
-
Jan 19th, 2018, 09:51 AM
#6
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|