1

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

Joe
  • 128
  • 1
  • 11

3 Answers3

3

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

Steve
  • 213,761
  • 22
  • 232
  • 286
  • Thanks so much. I'm not sure how to do a parameter-type query but I'm sure Google can help with that. This variable will never have single quotes as it is a file name, but thanks again! – Joe Jul 29 '15 at 12:27
  • 1
    Yes you could find a lot of examples also here on SO. There are numerous reasons to switch as soon as possible to a parameterized query. (Parsing problems, [Sql Injection](http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) (however that example will not work in Access), Syntax error caused by quoting complexity, etc...) – Steve Jul 29 '15 at 12:28
2

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'
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • Works now, please edit your first sentence though as it is still in regards to your first answer. Thank you! – Joe Jul 29 '15 at 12:32
2

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
Karlta05
  • 165
  • 10