Error: converting data type varchar to date

Question

0.00/5 (No votes)

See more:

Hi Everyone..
please help me in my project. i have create a procedure which i call to it in my aspx.cs file bellow is brief summary of my code.

What I have tried:

Here is my stored procedure:

SQL

CREATE PROCEDURE PLACE_DEMAND
(
@ITEM_ID INT,
@EMP_ID INT,
@DEMAND_DATE DATE,
@DEMAND_QUANTITY INT,
@DEMAND_STATUS VARCHAR (50),
@STATUS_DATE DATE
)
AS
BEGIN
INSERT INTO Demands (Item_ID,Emp_ID,Demand_Date,Demand_Quantity,Demand_Status,Status_Date) VALUES (@ITEM_ID,@EMP_ID,@DEMAND_DATE,@DEMAND_QUANTITY,@DEMAND_STATUS,@STATUS_DATE)
END

And bellow is my function which execute my procedure:

C#

public static void exec_insert_proc(string proc, string[] param)
 {
     SqlCommand cmd = new SqlCommand(proc, DBConnection());
     cmd.CommandType = CommandType.StoredProcedure;
     int i;
     for (i = 0; i <= param.Length - 1; i++)
     {
         string[] nme;
         nme = param[i].Split(':');
         cmd.Parameters.Add(nme [0], SqlDbType.VarChar, 50).Value = nme [1];
     }
     cmd.ExecuteNonQuery(); // error occured here which is Error converting data type varchar to date
 }

And Finaly this my function to which i am passing parameters

C#

string abc = Convert.ToString(DateTime.Now);
 string[] array = new string[]
 {
"@ITEM_ID:"+DDL_Select_Item.SelectedValue,"@EMP_ID:"+txt_Emp_ID.Text,"@DEMAND_DATE:"+txt_Date.Text,"@DEMAND_QUANTITY:"+txt_Quantity.Text,"@DEMAND_STATUS:"+txt_Status.Text,"@STATUS_DATE:"+abc 
};
 db.exec_insert_proc("dbo.PLACE_DEMAND", array);

Posted 20-Dec-16 20:19pm

Muhammd Aamir

Updated 20-Dec-16 20:37pm

v2

Add a Solution

2 solutions

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)

OriginalGriff · Accepted Answer · 2016-12-20T20:31:00

Simple: pass DateTime values as DateTime from your C# code to SQL, not as a string value.
When you pass a string to SQL and expect it to be inserted into a DATE column, SQL will use the datetime settings of it's server to convert it, not those of your client, or even your webserver. So if I enter todays date as "21/12/16" that makes perfect sense to me: "21st Day, 12th Month, 2016". But if the SQL server is set to the default Windows locale it expects that to be "21st Month, 12th day, 2016" or in ISO format as "2021, 12th Month, 16th Day" - and it can start to cause conversion errors, some of which will cause the error you have noticed because it is an invalid date. Additionally, if the user miskeys a value, the conversion will also fail.

Convert your strings to "natural" datatypes as soon as you can: Integer values to int, dates to DateTime using TryParse and the user culture and report problems direct to him instead of sending the data to SQL. Then pass those values as native datatypes to SQL so no further conversion is required. That way, you catch problems early, so they can be solved instead of inserting invalid data to your DB and causing massive problems to sort it out later when you notice the inconsistencies.

Kornfeld Eliyahu Peter · Accepted Answer · 2016-12-20T20:37:00

Solution 2

You are doing a big mistake - twice...
First of all you are passing binary values as string, which is always problem...
Just think about how many ways date can be represented as string...
The second mistake is converting everything to string and passing everything as string. You may be surprised but SQL can handle non-string values and the classes in .NET SQL library can handle them too...
So do yourself a favor and pass each and every value according the type...

You can create a common function that receives an array of objects and maps their real type to SQL types if you have to...

Posted 20-Dec-16 20:37pm

Kornfeld Eliyahu Peter

Comments

Muhammd Aamir 21-Dec-16 2:54am

thank you Kornfeld Eliyahu Peter what i will have to do to pass each and every value according the type please kindly show me some code

Kornfeld Eliyahu Peter 21-Dec-16 2:59am

You have this line:
cmd.Parameters.Add(nme [0], SqlDbType.VarChar, 50).Value = nme [1];
change SqlDbType.VarChar according to the real type of the object (some switch-case)...

Muhammd Aamir 21-Dec-16 3:25am

Ok thank you very much... have a great day