Error converting data type nvarchar to datetime

Question

1.00/5 (2 votes)

See more:

C#

protected void OnDelete(object sender, EventArgs e)
   {
       //Find the reference of the Repeater Item.
       RepeaterItem item = (sender as LinkButton).Parent as RepeaterItem;
       int orderId = int.Parse((item.FindControl("lblOrderID") as Label).Text);
       string custName = (item.FindControl("txtCustName") as TextBox).Text.Trim();
       string custPhone = (item.FindControl("txtPhone") as TextBox).Text.Trim();
       string custEmail = (item.FindControl("txtEmail") as TextBox).Text.Trim();
       string date = (item.FindControl("txtDate") as TextBox).Text.Trim();
       string cartAmount = (item.FindControl("txtCartAmount") as TextBox).Text.Trim();
       string cartDiscount = (item.FindControl("txtCartDiscount") as TextBox).Text.Trim();
       string totalPaid = (item.FindControl("txtTotalPaid") as TextBox).Text.Trim();
       string orderNumber = (item.FindControl("txtOrderNumber") as TextBox).Text.Trim();
       string orderStatus = (item.FindControl("txtOrderStatus") as TextBox).Text.Trim();




       string constr = ConfigurationManager.ConnectionStrings["KalzAgency"].ConnectionString;
       using (SqlConnection con = new SqlConnection(constr))
       {
           using (SqlCommand cmd = new SqlCommand("tblOrders_CRUD"))
           {
               cmd.CommandType = CommandType.StoredProcedure;
               cmd.Parameters.AddWithValue("@Action", "DELETE");
               cmd.Parameters.AddWithValue("@OrderID", orderId);
               cmd.Parameters.AddWithValue("@Name", custName);
               cmd.Parameters.AddWithValue("@MobileNumber", custPhone);
               cmd.Parameters.AddWithValue("@Email", custEmail);
               cmd.Parameters.AddWithValue("@DateOfPurchase", date);
               cmd.Parameters.AddWithValue("@CartAmount", cartAmount);
               cmd.Parameters.AddWithValue("@CartDiscount", cartDiscount);
               cmd.Parameters.AddWithValue("@TotalPaid", totalPaid);
               cmd.Parameters.AddWithValue("@OrderNumber", orderNumber);
               cmd.Parameters.AddWithValue("@OrderStatus", orderStatus);
               cmd.Connection = con;
               con.Open();
               cmd.ExecuteNonQuery();
               con.Close();
           }
       }
       this.BindCategoryReapter();
   }

What I have tried:

I set the data type in database with datetime for the date. When i click delete, the error occured. How can i solve this?

Posted 6-Apr-21 6:22am

Member 15100384

Updated 6-Apr-21 9:15am

PIEBALDconsult

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2021-04-06T06:24:00

Solution 1

You are passing a string to the @DateOfPurchase parameter. That string does not contain a valid date.

Use DateTime.TryParse[^] to attempt to convert the string to a date. If it succeeds, pass the parsed DateTime value to the parameter. If it fails, display an error to the user and abort the operation.

Posted 6-Apr-21 6:24am

Richard Deeming

Comments

Maciej Los 7-Apr-21 3:33am

5ed!

Member 15100384 13-Apr-21 13:17pm

where should i put it?

Richard Deeming 14-Apr-21 4:24am

Replace:

string date = (item.FindControl("txtDate") as TextBox).Text.Trim();

with:

DateTime date;
if (!DateTime.TryParse((item.FindControl("txtDate") as TextBox).Text.Trim(), out date))
{
    ... DISPLAY ERROR ...
    return;
}

Member 15100384 15-Apr-21 3:51am

I got the same error, "Error converting data type nvarchar to datetime."

Richard Deeming 15-Apr-21 4:41am

Then there's something else wrong with your database which you haven't shown in your question.

Member 15100384 15-Apr-21 4:47am

//This is my database.

CREATE TABLE tblOrders(
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[Email] [nvarchar](max) NULL,
[CartAmount] [money] NULL,
[CartDiscount] [money] NULL,
[TotalPaid] [money] NULL,
[DateOfPurchase] [datetime] NULL,
[Name] [nvarchar](200) NULL,
[MobileNumber] [nvarchar](50) NULL,
[OrderStatus] [nvarchar](50) NULL,
[OrderNumber] [nvarchar](50) NULL,
Constraint [FK_tblOrders_ToTable] FOREIGN KEY ([UserID]) REFERENCES [tblUsers] ([uid])
)

//This is my stored procedure.

CREATE PROCEDURE [dbo].[tblOrders_CRUD]
@Action VARCHAR(10)
,@OrderID INT = NULL
,@UserID INT = NULL
,@Email NVARCHAR(MAX) = NULL
,@CartAmount MONEY = NULL
,@CartDiscount MONEY = NULL
,@TotalPaid MONEY = NULL
,@DateOfPurchase DATETIME = NULL
,@Name NVARCHAR(200) =NULL
,@MobileNumber NVARCHAR(50) = NULL
,@OrderStatus NVARCHAR(50) = NULL
,@OrderNumber NVARCHAR(50) = NULL

AS
BEGIN
SET NOCOUNT ON;

--SELECT
IF @Action = 'SELECT'
BEGIN
SELECT OrderID, UserID, Email, CartAmount, CartDiscount, TotalPaid, DateOfPurchase, Name, MobileNumber, OrderStatus, OrderNumber
FROM tblOrders
END

--INSERT
IF @Action = 'INSERT'
BEGIN
INSERT INTO tblOrders(OrderID, UserID, Email, CartAmount, CartDiscount, TotalPaid, DateOfPurchase, Name, MobileNumber, OrderStatus, OrderNumber )
VALUES (@OrderID, @UserID, @Email, @CartAmount, @CartDiscount, @TotalPaid, @DateOfPurchase, @Name, @MobileNumber, @OrderStatus, @OrderNumber )
END

--UPDATE
IF @Action = 'UPDATE'
BEGIN
UPDATE tblOrders
SET UserID = @UserID, Email=@Email, CartAmount=@CartAmount, CartDiscount=@CartDiscount, TotalPaid=@TotalPaid, DateOfPurchase=@DateOfPurchase, Name=@Name, MobileNumber=@MobileNumber, OrderStatus=@OrderStatus, OrderNumber=@OrderNumber
WHERE OrderID = @OrderID
END

--DELETE
IF @Action = 'DELETE'
BEGIN
DELETE FROM tblOrders
WHERE OrderID = @OrderID
END
select * from tblOrders
END
GO

Richard Deeming 15-Apr-21 5:43am

Your code is passing @Action = 'DELETE', so there's nothing in the stored procedure that touches a datetime value other than the parameter.

Either something else is going on that you haven't shown, or you haven't updated your C# code to pass the date as a DateTime instead of a String.