Click here to Skip to main content
14,971,553 members
Please Sign up or sign in to vote.
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
Updated 4-Feb-21 10:14am
v6
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

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
   
v2
Comments
aayu 6-Oct-10 8:45am
   
heheheheheheh Mohd Wasif error is solve thank you very very much.

But there is one problem this statement cmd.Parameters.AddWithValue("@clientname ", comboBox1.SelectedItem.ToString()); gives error so i have change it to this cmd.Parameters.AddWithValue("@clientname ", cmdclientname.Text);

and second it taking time which is in 00.00.000 both order and delivery time please help me for this also
Pass Directly values of dateTimePicker1,dateTimePicker2 in insert statement
   
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
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
   
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
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
   
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");
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).
   
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
   
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
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
   
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
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
   
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
   
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

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900