char to Datetime conversion

Dear Experts,

I've got  trouble  from char to Datetime conversion.

This is my code :  

        OleDbParameter parameterOrderDate = new OleDbParameter("@p_OrderDate", OleDbType.Date, 26);
        parameterOrderDate.Value = Convert.ToDateTime(this.OrderDate.Text);
        myCommand.Parameters.Add(parameterOrderDate);

This is the Error Message:

Data.OleDb.OleDbException: Error converting data type char to datetime. at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at ORDER.UI.AddOrderPage.AddRecord(Object sender, EventArgs e) in t:\PFMC\ORDER\Order\AddOrderPage.aspx.cs:line 85

Any help is greatly appreciated

Thanks.
JaimeJegoniaAsked:
Who is Participating?
 
TheAvengerCommented:
No, your parameter in the SP is already datetime and is fine.

The problem is that you need to add the parameters in the same order they are in the SP. So you need to change the places of the creation and addition of the parameters @p_RTPID and @p_OrderDate to match the SP. Then everything works fine.
0
 
JaimeJegoniaAuthor Commented:
This the Stored Procedure:

-- Creates a new record in the [Order] table.
CREATE PROCEDURE [dbo].[pORDEROrderAdd]
    @p_OrderID nchar(10),
    @p_OrderDate datetime,
    @p_RTPID nchar(10)
AS
BEGIN
    INSERT
    INTO [Order]
        (
            [OrderID],
            [OrderDate],
            [RTPID]
        )
    VALUES
        (
             @p_OrderID,
             @p_OrderDate,
             @p_RTPID
        )

END
0
 
TheAvengerCommented:
Try changing the type of the parameter from OleDbType.Date to OleDbType.DBDate
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
TheAvengerCommented:
Or you can try even OleDbType.DBTimeStamp which should save the time as well
0
 
JaimeJegoniaAuthor Commented:
I tried both DBDate and DBTimeStamp - failed with the same error message.
0
 
TheAvengerCommented:
What is your database - SQL Server?
Can you post your whole code where the command is created?
Is there a specific reason you are using OleDbXxx instead of SqlXxxx?
0
 
JaimeJegoniaAuthor Commented:

I'm having multiple database connections, using SQL 2005.

This is the code ASP NET 2.0
##########################################
 public void AddRecord(object sender, EventArgs e)
  {
      // Create Instance of Connection and Command Object
        OleDbConnection myConnection = new OleDbConnection(ConfigurationManager.AppSettings["DatabaseOrders1"]);
        OleDbCommand myCommand = new OleDbCommand("spAddRecord", myConnection);
        // Mark the Command as a SPROC
        myCommand.CommandType = System.Data.CommandType.StoredProcedure;

        OleDbParameter parameterOrder = new OleDbParameter("@p_OrderID", OleDbType.Char, 10);
        parameterOrder.Value = this.OrderID.Text;
        myCommand.Parameters.Add(parameterOrder);

        // Add Parameters to SPROC
        OleDbParameter parameterRTP = new OleDbParameter("@p_RTPID", OleDbType.Char, 10);
        parameterRTP.Value = this.RTPID.SelectedValue;
        myCommand.Parameters.Add(parameterRTP);
       
           // Add Parameters to SPROC
        OleDbParameter parameterOrderDate = new OleDbParameter("@p_OrderDate", OleDbType.DBTimeStamp, 26);
        parameterOrderDate.Value = Convert.ToDateTime(this.OrderDate.Text);
        myCommand.Parameters.Add(parameterOrderDate);
       
               // Open the connection and execute the Command
            myConnection.Open();
            myCommand.ExecuteNonQuery();
            myConnection.Close();

          }
 

Thanks.
0
 
TheAvengerCommented:
I think you don't need a length of the order date parameter, so it can be new OleDbParameter("@p_OrderDate", OleDbType.DBTimeStamp). Does this help?
0
 
JaimeJegoniaAuthor Commented:
TheAvenger,

I tried with the FF: with errors

new OleDbParameter("@p_OrderDate", OleDbType.DBTimeStamp).
new OleDbParameter("@p_OrderDate", OleDbType.DBDate).
new OleDbParameter("@p_OrderDate", OleDbType.Date).

Do you think I need conversion in the SP?

Thanks.
0
 
JaimeJegoniaAuthor Commented:
TheAvenger,

That's great! You made my day!  So it turned out that all these are also correct:

new OleDbParameter("@p_OrderDate", OleDbType.DBTimeStamp).
new OleDbParameter("@p_OrderDate", OleDbType.DBDate).
new OleDbParameter("@p_OrderDate", OleDbType.Date).

Thank you very much.
0
 
JaimeJegoniaAuthor Commented:
BTW,

What's the most universal format among the 3 types?


Thanks.
0
 
TheAvengerCommented:
Glad I could help.
0
 
TheAvengerCommented:
I would use Date if it works fine. In MSDN it's written that Date keeps everything in a double variable while the others use strings.
0
 
JaimeJegoniaAuthor Commented:
That's what I did. I tried my original code and it worked as well:

(OleDbParameter parameterOrderDate = new OleDbParameter("@p_OrderDate", OleDbType.Date, 26);

It was really a stupid mistake on my part but anyway, it's good to work with somebody like you and It would be a lesson for me to learn too - I thought that was something new with SQL 2005.


Thanks once again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.