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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
TheAvengerCommented:
Try changing the type of the parameter from OleDbType.Date to OleDbType.DBDate
TheAvengerCommented:
Or you can try even OleDbType.DBTimeStamp which should save the time as well
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

JaimeJegoniaAuthor Commented:
I tried both DBDate and DBTimeStamp - failed with the same error message.
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?
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.
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?
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.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
JaimeJegoniaAuthor Commented:
BTW,

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


Thanks.
TheAvengerCommented:
Glad I could help.
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.
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.