Conversion failed when converting datetime from character string. please help to solve this

Question

1.00/5 (1 vote)

See more:

I get the following SqlException when i am trying to insert into db from C# window application.

Conversion failed when converting datetime from character string.

This is my SQL Query:

SQL

CREATE TABLE orderbooking
(
	orderdate datetime ,
	clientname varchar(40) ,
	deliverylocation varchar(30) ,
	deliverydate datetime
)

Sqlexception on cmd.ExecuteNonQuery();
This is my Add button code:

C#

    {
    SqlConnection cnn = new SqlConnection(@"Server=NGENIOUS-WSSDEV\SQLEXPRESS;Database=MIC;Trusted_Connection= True");
    cnn.Open();
    SqlParameter p1 = new SqlParameter("@orderdate", dateTimePicker1.Text);
    SqlParameter p2 = new SqlParameter("@clientname", comboBox1.Text);
    SqlParameter p3 = new SqlParameter("@Delivery Location", textBox2.Text);
    SqlParameter p4 = new SqlParameter("@Delivery Date", dateTimePicker2.Text);
    string query = "insert into orderbooking values('" + p1.Value + "','" + p2.Value + "','" + p3.Value + "','" + p4.Value + "')";
    SqlCommand cmd = new SqlCommand(query, cnn);

    cmd.ExecuteNonQuery();
    cnn.Close();

    button2.Enabled = false;
}

I have added this code:

SQL

SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]

but same error please help :(

Timing is not displaying in sql server database :(

Thanks in Advance :rose::rose:

Posted 6-Oct-10 0:37am

aayu

Updated 4-Feb-21 10:14am

Toli Cuturicu

v6

Add a Solution

Comments

NMehta83 6-Oct-10 6:39am

can you show the date here? so get the idea of your date format.

Richard MacCutchan 6-Oct-10 6:44am

Check the value and format of "dateTimePicker1.Text", I have a feeling that SQL needs this to be in a particular order (maybe mm/dd/yy).

aayu 6-Oct-10 6:53am

the datetimepicker1.text format is (day, mm/dd/yy)

ravitv 7-Mar-21 4:13am

Sorry to spin a 10yr old Thread.
How to concatenate values from 3 combobox containing DD MM YYYY and then insert it in SQL Server 2008 using C#
What I did was...

protected void btnSubmit_Click1(object sender, EventArgs e)
{

if (IsControlsValid())
{
SqlConnection cn = new SqlConnection(@"Data Source=Admin-PC\SQLEXPRESS;Attac.........mdf;Integrated Security=True;User Instance=True");

String query = "Insert into EmployeeMaster(UserName,Password,BirthDate,Email) values(@UserName,@Password,@dob,@Email)";
SqlCommand cmd = new SqlCommand(query, cn);

int year = Convert.ToInt32(drpYYYY.SelectedItem.ToString());
int month = Convert.ToInt32(drpMM.SelectedItem.ToString());
int day = Convert.ToInt32(drpDD.SelectedItem.ToString());

DateTime dob = new DateTime(day, month, year);
cmd.Parameters.AddWithValue("@UserName", txtuname.Text);
cmd.Parameters.AddWithValue("@Password", txtupass.Text);
cmd.Parameters.AddWithValue("@BirthDate", dob);
cmd.Parameters.AddWithValue("@Email", emailID.Text);

try
{
cn.Open();
cmd.ExecuteNonQuery();
lblError.ForeColor = System.Drawing.Color.Red;
lblError.Text = "Employee Details Entered Successfully";
}
catch (Exception)
{
//Console.WriteLine("Error Generated. Details: " + e.ToString());
}
finally
{
cn.Close();
}
}

I dont use dateTimePicker - I use 3 Combobox.
Please Help.
Thanks
Ravi

9 solutions

Solution 1

Pass Directly values of dateTimePicker1,dateTimePicker2 in insert statement

Posted 6-Oct-10 0:52am

Mohd Wasif

Comments

aayu 6-Oct-10 6:54am

i did that but it gives me Exception

Mohd Wasif 6-Oct-10 7:32am

Ok wait for few moment i am checking it

Solution 4

DateTime date1 = Convert.ToDateTime(dateTimePicker1.Text);
SqlCommand cmd = new SqlCommand("insert into dep(DepName,Date)values(@d,@da)", con);

cmd.Parameters.AddWithValue("@d", textBox1.Text);
cmd.Parameters.AddWithValue("@da", date1);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
it is running use it .

Good Luck.

no need to make changes in table defination

Posted 6-Oct-10 1:54am

Mohd Wasif

Comments

aayu 6-Oct-10 8:09am

i am getting error on this cmd.ExecuteNonQuery();

Mohd Wasif 6-Oct-10 8:12am

just write query in sqlcommand and parameter as i did surely u will get the result

aayu 6-Oct-10 8:17am

see in sql i have written the select converter query and then the code which you have given that also i have written but i am still in the same boat cmd.ExecuteNonQuery(); exception

aayu 6-Oct-10 8:19am

and second error incorrect syntax near by "="

Mohd Wasif 6-Oct-10 8:22am

just give your wriiten code in my format

Solution 5

Have u convert datetimepicker2 in datetimeformat or not

SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD] where u are using this code?

if u r using this then use like it.

SELECT CONVERT(VARCHAR(10), orderdate , 120) AS OrderDate,
CONVERT(VARCHAR(10), deliverydate, 120) AS Deliverydate from orderbooking

Posted 6-Oct-10 2:18am

Mohd Wasif

Updated 6-Oct-10 2:20am

v2

Comments

aayu 6-Oct-10 8:21am

this query is working but when i am running the program same cmd.ExecuteNonQuery();

aayu 6-Oct-10 8:26am

r u talking about sql query then my query is
orderdate datetime ,
clientname varchar(40) ,
deliverylocation varchar(30) ,
deliverydate datetime
over here it is datetime format

aayu 6-Oct-10 8:27am

in C# on the click of datetimepicker2 i have written this code

Convert.ToDateTime(dateTimePicker1.Text).ToString("dd-MM-yyyy");

Solution 7

Convert.ToDateTime(dateTimePicker1.Text).ToString("dd-MM-yyyy")

Here U did a mistake ?

U don't need to write
ToString("dd-MM-yyyy") after Convert.ToDateTime(dateTimePicker1.Text).

Posted 6-Oct-10 2:45am

Mohd Wasif

Solution 8

cmdclientname is combobox or textbox if textbox then u have written right if it is combobox then change it like cmdclientname.SelectedItem.ToString();
secondly have u checked in database which value is storing in both dates if it is goinning 00:00:00 format then at run time time check what values are showing in both datetimepicker

Posted 6-Oct-10 2:56am

Mohd Wasif

Comments

aayu 6-Oct-10 9:09am

for your first question i have use combo box but is giving me error may be because i have not put any list in combo box collection.

i have check in database it is giving me 00:00:000 and at run time i am giving today's date

Solution 9

Ok I got Your Point .

u don't need to worry where ever u have to display the date let me know
I will let u know how to extract date from sqlServer in any format
Ok

Posted 6-Oct-10 3:04am

Mohd Wasif

Comments

aayu 6-Oct-10 9:11am

orderdate and delivery date i need time

aayu 6-Oct-10 9:18am

i have change cmdclientname.SelectedItem.ToString(); and it is running properly
now only timing is remaining

Solution 10

Look if u have choosen datetime datatype for orderdate ,deliverydate
then it will take time along with date in sql server.So,don't get worried if u have to select date from sql server then u have to run query as below

select convert(varchar,orderdate,104) as OrderDate ,convert(varchar,deliverydate,104)
DeliveryDate
from orderbooking

Posted 6-Oct-10 19:42pm

Mohd Wasif

Solution 12

i solved this problem by changing the date time from regional settings

make the format English United states
short date :yyyy-MM-dd

also i entered the data
like insert into table(,,,deliveryDate) values(,,,datetimePicker1.Value.toShortDateString())

i wish this helps

Posted 4-Feb-21 10:14am

Member 13639193

Updated 4-Feb-21 10:17am

v2

Comments

CHill60 5-Feb-21 4:07am

That is NOT format "English United States" which is MM/dd/yyyy. You have used ISO 8601 format - which is ironically the correct approach.
There is no need to use toShortDateString() if you use the correct column type, you can use the date directly from the datepicker.
You have brought nothing new to this 10 year thread

Richard Deeming 5-Feb-21 5:50am

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

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)

Mohd Wasif · Accepted Answer · 2010-10-06T02:29:00

DateTime date1 = Convert.ToDateTime(dateTimePicker1.Text);
DateTime date2 = Convert.ToDateTime(dateTimePicker2.Text);
SqlCommand cmd = new SqlCommand("insert into dep(orderdate ,clientname ,deliverylocation ,deliverydate )values(@orderdate,@clientname ,@deliverylocation ,@deliverydate )", con);

cmd.Parameters.AddWithValue("@orderdate",date1 );
cmd.Parameters.AddWithValue("@clientname ", comboBox1.SelectedItem.ToString());
cmd.Parameters.AddWithValue("@deliverylocation ", textBox2.Text);
cmd.Parameters.AddWithValue("@deliverydate ", date2);
con.Open();
cmd.ExecuteNonQuery();
con.Close();

Just Copy and Paste on buttonClick

It will Run

Conversion failed when converting datetime from character string. please help to solve this

9 solutions

Solution 6

Solution 1

Solution 4

Solution 5

Solution 7

Solution 8

Solution 9

Solution 10

Solution 12

Add your solution here

Preview 0

Existing Members

...or Join us