none
No value given for one or more parameters message occurs when attempting to update an Access file. Why? RRS feed

  • Question

  •  

    I have used various tips found for this error by none have worked. I tried placing the variables in with "?" and that  didn't work.  What am I doing wrong?  Here is my code:

     

     

     

    Dim myConnection As New OleDb.OleDbConnection _

                          ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\PR.mdb;User Id=Admin;Password=;")

    Dim myQuery As String = "Update Records Set "

    myQuery += " TC = " & cotc & ",  [Issue Date] = " & cIssDat & ", [Return Date] = " & cRetDat & "  "

                    myQuery += " WHERE [Rec #] = " & cdesID & " & TC = O "

    myConnection.Open()

    Dim myCommand As New OleDb.OleDbCommand(myQuery, myConnection)             myCommand.ExecuteNonQuery()

    myConnection.Close()

     

     




    New to VS2005

    Tuesday, October 2, 2007 1:19 PM

Answers

  • Thanks Paul for your insight.  It made me take a closer look at my OleDbDataAdapter and OleDbCommand.  Notice that there were a few errors in the adapter that were not flagged as errors under VS 2005.  Once I corrected the errors....updates occurred with ease.  Thx

    Wednesday, October 10, 2007 2:30 PM

All replies

  • Could you post the actual SQL statement (containing all of the values) before it is executed?

     

    Tuesday, October 2, 2007 1:35 PM
  •  

    Removed the ampersands before the variables but the ctoc (O), cIssDat (10/02/2007) and cRetDat (10/02/2007) are the variables.
    Tuesday, October 2, 2007 1:46 PM
  • Well, so far all I have to go on are the dates, which need to be enclosed in pound signs (#) when inserted into a SQL statement (assuming you're working with a Date data type).

     

    Tuesday, October 2, 2007 1:51 PM
  • In Access string values must be enclosed in single quotes ' and dates must be enclosed in #

    So using string concatenation as you are doing, you would want to do this for your dates:

    "[Issue Date] = #" & cIssDat & "#, "


    Edit: Oops Paul beat me to it.
    Tuesday, October 2, 2007 1:56 PM
  •  Do I need to put pound symbols around the date if I used string variables?  Used string variables...

     

     

    Dim myQuery As String = "Update Records Set TC = 'R', [Issue Date] = '10/02/2007', [Return Date] = '10/2/2007'

    Where [Rec #] = '118'  AND TC = 'O' "

     

     

    Tuesday, October 2, 2007 2:04 PM
  • No, if the data type of the column in the table is Text then single quotes is appropriate. However, keep in mind that you're not actually comparing date values but string characters.

     

    Perhaps there's a typo in your example above but I see a brace rather than a bracket enclosing "Rec #".

     

    Tuesday, October 2, 2007 2:15 PM
  •  

    was correcting the typo while you were writing your message
    Tuesday, October 2, 2007 2:18 PM
  • Now I am confuse....Correct me if I am wrong but the field name = variable is not a comparison but a value given for that column in that row right?

     Ex: after the SET of the sql statement  TC = 'R', .....

     

    Doesn't this mean that the value of R will be placed in the TC column of this row, right?

    Tuesday, October 2, 2007 3:14 PM
  • Yes, that is correct. I used incorrect terminology. I should have just said that you're assigning a string literal which represents a date value instead of an actual date value.

     

    Tuesday, October 2, 2007 3:20 PM
  • Isn't that okay?  That wouldn't have caused the 'No value given for one or more parameters' message on the ExecuteNonQuery or ExecuteSecular().  What does that error mean?  It does not relate to the collection in the properties. 

    Tuesday, October 2, 2007 3:44 PM
  • At this point I can't tell you what was causing the error because I haven't seen a complete SQL statement (with all of the values). This error can be misleading but I believe that in your case it indicates that there is a column name or value in the SQL statement that the database engine cannot resolve, because it doesn't exist in the table.

     

    I would double check your column names to make certain that they match up with those in your table. You may also want to post the complete SQL statement (with values) if you're still encountering this error.

     

     

    Tuesday, October 2, 2007 3:55 PM
  • Ok that didn't work....so ..I posted the SQL statement with its values above but here it is again.  I have checked both column name and they match.   

     

    SQL with values:

    Update Records Set TC = R, [Issue Date] = ""100407"", [Return Date] = ""100507"" where [Rec #] = ""118"" And TC = O

    remember that the original SQL statement is using variables for the values.

     

    I have noticed that i cannot preview the OLEDBDataAdapter data via query builder but instead receive the error message.

     

    Form1.OleDbUpdateCommand could not be previewed System.Data.OleDb.OleDbDataAdapter internal error: invalid parameter accessor; 2

    BADBINDINFO

     

    Is there an easier way to update an Access file.  It has already been four days.

     

     

    Friday, October 5, 2007 5:39 PM
  • Any assist to the above will be helpful.

    Tuesday, October 9, 2007 1:44 PM
  • The date values should only be enclosed in single quotes so I'm not exactly sure how you got two sets of double quotes. With respect to TC, assuming R and O are literal values and not variables then they should be enclosed in single quotes as well:

     

    Update Records Set TC = 'R', [Issue Date] = '100407', [Return Date] = '100507' where [Rec #] = '118' And TC = 'O'

     

     

    Tuesday, October 9, 2007 4:32 PM
  • Thanks Paul for your insight.  It made me take a closer look at my OleDbDataAdapter and OleDbCommand.  Notice that there were a few errors in the adapter that were not flagged as errors under VS 2005.  Once I corrected the errors....updates occurred with ease.  Thx

    Wednesday, October 10, 2007 2:30 PM