saaHelp

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()




Re: .NET Framework Data Access and Storage No value given for one or more parameters message occurs when attempting to update an Access file. Why?

Paul P Clement IV

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






Re: .NET Framework Data Access and Storage No value given for one or more parameters message occurs when attempting to update an Access file. Why?

saaHelp

Removed the ampersands before the variables but the ctoc (O), cIssDat (10/02/2007) and cRetDat (10/02/2007) are the variables.




Re: .NET Framework Data Access and Storage No value given for one or more parameters message occurs when attempting to update an Access file. Why?

Paul P Clement IV

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).






Re: .NET Framework Data Access and Storage No value given for one or more parameters message occurs when attempting to update an Access file. Why?

kbradl1

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.




Re: .NET Framework Data Access and Storage No value given for one or more parameters message occurs when attempting to update an Access file. Why?

saaHelp

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' "






Re: .NET Framework Data Access and Storage No value given for one or more parameters message occurs when attempting to update an Access file. Why?

Paul P Clement IV

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 #".






Re: .NET Framework Data Access and Storage No value given for one or more parameters message occurs when attempting to update an Access file. Why?

saaHelp

was correcting the typo while you were writing your message




Re: .NET Framework Data Access and Storage No value given for one or more parameters message occurs when attempting to update an Access file. Why?

saaHelp

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






Re: .NET Framework Data Access and Storage No value given for one or more parameters message occurs when attempting to update an Access file. Why?

Paul P Clement IV

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.






Re: .NET Framework Data Access and Storage No value given for one or more parameters message occurs when attempting to update an Access file. Why?

saaHelp

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.






Re: .NET Framework Data Access and Storage No value given for one or more parameters message occurs when attempting to update an Access file. Why?

Paul P Clement IV

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.






Re: .NET Framework Data Access and Storage No value given for one or more parameters message occurs when attempting to update an Access file. Why?

saaHelp

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.






Re: .NET Framework Data Access and Storage No value given for one or more parameters message occurs when attempting to update an Access file. Why?

saaHelp

Any assist to the above will be helpful.






Re: .NET Framework Data Access and Storage No value given for one or more parameters message occurs when attempting to update an Access file. Why?

Paul P Clement IV

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'