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);
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.
KhD
Member
97 Points
42 Posts
Error while trying to pass NULL to a timestamp field
Oct 12, 2011 04:07 AM|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[0].ToString()));
accessCmdUpdate.Parameters.Add(new OleDbParameter("@1", dr[1].ToString()));
accessCmdUpdate.Parameters.Add(new OleDbParameter("@2", convertedText));
if (dr[3] != System.DBNull.Value)
accessCmdUpdate.Parameters.Add(new OleDbParameter("@3",OleDbType.DBTimeStamp,100,ParameterDirection.Input,true,(byte)(100),(byte)(0),"xxxdatetime",DataRowVersion.Current,Convert.ToDateTime(dr[3])));
else
accessCmdUpdate.Parameters.Add(new OleDbParameter("@3", OleDbType.DBTimeStamp, 100, ParameterDirection.Input, true, (byte)(100), (byte)(0), "xxxdatetime", DataRowVersion.Current, DBNull.Value));
accessCmdUpdate.ExecuteNonQuery();
}
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.
Please help!
hans_v
All-Star
35986 Points
6550 Posts
Re: Error while trying to pass NULL to a timestamp field
Oct 12, 2011 08:20 AM|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
http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access
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.
http://www.mikesdotnetting.com/Article/92/MS-Access-Date-and-Time-with-ASP.NET
KhD
Member
97 Points
42 Posts
Re: Error while trying to pass NULL to a timestamp field
Oct 13, 2011 08:40 AM|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!