Last post Oct 13, 2011 08:40 AM by KhD
Oct 12, 2011 04:07 AM|KhD|LINK
I am trying to insert records into Access table from SQL Server table.
For each row in SQL Server table, I'm checking for a condition that if timestamp field is NULL, then insert NULL into corresponding Access field as well.
Pasting a small code snippet here:
foreach (DataRow dr in dra) //Each datarow in SQL Server table
OleDbCommand accessCmdUpdate = new OleDbCommand("update xxx set xxxID=@0,xxxNotes=@2,xxxdatetime=@3 where xxxID=@1", AccessConnection);
accessCmdUpdate.Parameters.Add(new OleDbParameter("@0", dr.ToString()));
accessCmdUpdate.Parameters.Add(new OleDbParameter("@1", dr.ToString()));
accessCmdUpdate.Parameters.Add(new OleDbParameter("@2", convertedText));
if (dr != System.DBNull.Value)
accessCmdUpdate.Parameters.Add(new OleDbParameter("@3", OleDbType.DBTimeStamp, 100, ParameterDirection.Input, true, (byte)(100), (byte)(0), "xxxdatetime", DataRowVersion.Current, DBNull.Value));
I have tried inserting DbNull.Value also. But it doesn't work. I get the following error:
System.Data.OleDb.OleDbException: No value given for one or more required parameters.
Oct 12, 2011 08:20 AM|hans_v|LINK
In your command you seem to use 4 parameter (@0, @2, @3 and @1), and you're passimg 4 parameters as well. But still you get the message "No value given for one or more required parameters". This ussually means that you mispelled one or more fieldnames. Every
identifier that Access doesn't recognize as a fieldname, is interpreted as a parameter. So check you command?
Another issue is the order of the parameters. In your Command, the order is @0, @2, @3, @1 but your adding the parameters in the order @0, @1, @2, @3. OleDb parameters must be added in the order they (first) appear in the command, so you need to change the
order you add the parameters
Also, what datatype is the xxxdatetime field in Access. You're talking about a timestamp field, but in SQL server the datattype
timestamp isn't (other than te name suggest) NOT a (date)time, and you can't convert it to a datettime. If it is a datettime in SQL Server, you need to be aware that in Access, datetimes cannot contain milliseconds.
Oct 13, 2011 08:40 AM|KhD|LINK
Thanks for the information and the links hans_v. They seem to be very helpful!
The problem is solved.
I changed the order of parameters as you mentioned, and instead of Convert.ToDateTime(), I used DateTime.Parse() and it worked!