locked
Error while trying to pass NULL to a timestamp field RRS feed

  • Question

  • User780826891 posted

    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!

    Wednesday, October 12, 2011 12:07 AM

Answers

  • User-1199946673 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 12, 2011 4:20 AM

All replies

  • User-1199946673 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 12, 2011 4:20 AM
  • User780826891 posted

    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!

    Thursday, October 13, 2011 4:40 AM