?
Solved

char to Datetime conversion

Posted on 2006-04-03
14
Medium Priority
?
495 Views
Last Modified: 2012-08-14
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.
0
Comment
Question by:JaimeJegonia
  • 7
  • 7
14 Comments
 

Author Comment

by:JaimeJegonia
ID: 16367654
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
 
LVL 20

Expert Comment

by:TheAvenger
ID: 16367880
Try changing the type of the parameter from OleDbType.Date to OleDbType.DBDate
0
 
LVL 20

Expert Comment

by:TheAvenger
ID: 16367889
Or you can try even OleDbType.DBTimeStamp which should save the time as well
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:JaimeJegonia
ID: 16367974
I tried both DBDate and DBTimeStamp - failed with the same error message.
0
 
LVL 20

Expert Comment

by:TheAvenger
ID: 16367988
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
 

Author Comment

by:JaimeJegonia
ID: 16368014

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
 
LVL 20

Expert Comment

by:TheAvenger
ID: 16368036
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
 

Author Comment

by:JaimeJegonia
ID: 16368098
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
 
LVL 20

Accepted Solution

by:
TheAvenger earned 1600 total points
ID: 16368190
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
 

Author Comment

by:JaimeJegonia
ID: 16368261
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
 

Author Comment

by:JaimeJegonia
ID: 16368266
BTW,

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


Thanks.
0
 
LVL 20

Expert Comment

by:TheAvenger
ID: 16368268
Glad I could help.
0
 
LVL 20

Expert Comment

by:TheAvenger
ID: 16368284
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
 

Author Comment

by:JaimeJegonia
ID: 16368376
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question