CODEWITHSUNDEEP
vb.net
Joe
Joe

Reputation: 128

No value given for one or more required parameters in Access

I am attempting to update an item in my table and it is not working, I keep getting this error "no value given for one or more required parameters"

I know it's not my connection settings to the Access MDB database as I've been able to communicate with it already. Here is my function:

Public Sub UpdateDB(ByVal dbTable As String, ByVal updateColumn As String, ByVal updateItem As String, ByVal keyCol As String, ByVal keyItem As String)
    'Update items in DB
    aCommand.CommandType = CommandType.Text
    aCommand.CommandText = String.Format("UPDATE {0} SET {1} = '{2}' WHERE {3} = {4}", dbTable, updateColumn, updateItem, keyCol, keyItem)
    aCommand.Connection = aConnection
    aConnection.Open()
    'MsgBox(aCommand.CommandText)
    Try
        aCommand.ExecuteNonQuery()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try

    aConnection.Close()
End Sub

And my function call: VMD.UpdateDB("Bushing", "Length", "0.98123", "FileName", "Bushing1")

Thanks, any other info needed just let me know.

Edit: Picture of my table:

enter image description here

Upvotes: 1

Views: 59

Answers (3)

Steve
Steve

Reputation: 216293

Your command text is the following

 String.Format("UPDATE {0} SET {1} = '{2}' WHERE {3} = {4}", ....

and would be changed by formatting to

UPDATE Bushing SET Length = '0.98123' WHERE FileName = Bushing1

Notice how Bushing1 is not between single quotes. Access tries to interpret this string as a column name, but there is no column with that name, so it should be a parameter, but there is no parameter for it. It gives up and raises the errror message. You could easily fix it putting quotes around the '{4}'

But this will only hide your problems under the carpet because if you have a parameter containing a single quote you will get again an error message. I really suggest you to use a parameterized query approach instead of this form of string concatenation

Upvotes: 3

Rahul
Rahul

Reputation: 77876

Your query not working cause you don't have a single quote around value in WHERE condition. With that your query looks like

UPDATE Bushing SET Length = 0.98123 WHERE FileName = Bushing1

Your query shroud look like

string query = String.Format("UPDATE {0} SET {1} = '{2}' WHERE {3} = '{4}'", dbTable, updateColumn, updateItem, keyCol, keyItem);

It will result in 

UPDATE Bushing SET Length = '0.98123' WHERE FileName = 'Bushing1'

Upvotes: 2

Karlta05
Karlta05

Reputation: 165

Length is a Access Function. You won't be able to just send "Length" to reference the column name. What you need to do is surround the column name with square brackets. Example: 

  "[Length]" // Instead of "Length"

Your code example will be as follows:

 Public Sub UpdateDB(ByVal dbTable As String, ByVal updateColumn As String, ByVal updateItem As String, ByVal keyCol As String, ByVal keyItem As String)
'Update items in DB
aCommand.CommandType = CommandType.Text
aCommand.CommandText = String.Format("UPDATE {0} SET {1} = '{2}' WHERE [{3}] = {4}", dbTable, updateColumn, updateItem, keyCol, keyItem)
aCommand.Connection = aConnection
aConnection.Open()
'MsgBox(aCommand.CommandText)
Try
    aCommand.ExecuteNonQuery()
Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

aConnection.Close()
End Sub

Upvotes: 2

Related Questions