Error converting from varchar to datetime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error converting from varchar to datetime

Author

Topic

koutei
Starting Member

7 Posts

Posted - 2012-03-22 : 22:22:32

Hi Everybody, i am using VS 2010 and SQL server 08 r2 i got a issue when i try to save some data entered from a .net form to a table. The error is saying dat, there has been a error while converting the date(from a datetimepicker whick is considered varchar) to the datetime corresponding column in the table.
there is my code for the saving button :
*******************************************
cmd6 = conn.CreateCommand
cmd6.CommandText = "INSERT INTO Employees_Details (EmpID, FirstName, Surname, Gender, DateOfBirth, DateOfJoin, PANNumber, Address, PhoneNoHome, PhoneNoMob, Department, CivilStatus, Qualification, Title) VALUES('" & Trim(txtEmpID.Text) & "','" & Trim(txtFirstName.Text) & "','" & Trim(txtSurname.Text) & "','" & Trim(cboGender.Text) & "','" & Trim(dtpDateOfBirth.Value) & "','" & Trim(dtpDateOfJoin.Value) & "','" & Trim(txtPANNumber.Text) & "','" & Trim(txtAddress.Text) & "','" & Trim(txtPhoneHome.Text) & "','" & Trim(txtPhoneMob.Text) & "','" & Trim(cboDepartment.Text) & "','" & Trim(cboCivilStatus.Text) & "','" & Trim(txtQualification.Text) & "','" & Trim(cboTitle.Text) & "')"
check = cmd6.ExecuteReader.RecordsAffected()
If check > 0 Then
MessageBox.Show("The Record Was Saved Successfully!", "Successfull Registration", MessageBoxButtons.OK, MessageBoxIcon.Information)
conn.Close()
*********************************
Could you help me correct it please, Thank you for your concern

Les choses ne sont pas toujours comme elles paraissent!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-22 : 22:33:21

see
http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

so make sure you convert datevalues like dtpDateOfBirth to iso format (YYYYMMDD) before you pass it to INSERT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

koutei
Starting Member

7 Posts

Posted - 2012-03-22 : 22:42:03

Thanks you, i just read the post u gave me, but i still can convert it. I tried converting the value of the dtpicker(i may have misused the CONVERT function). I tried to set a predefined format for the dtppicker but it doesn't work either

Les choses ne sont pas toujours comme elles paraissent!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-22 : 23:12:57

sorry but i cant see where you used the convert code in query above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

koutei
Starting Member

7 Posts

Posted - 2012-03-22 : 23:16:18

i didn't mentien in the code above because i don't know how to use it properly, i got error every time i used it so i gave up. Do you know how ot convert the varchar of the dtpicker to into a date variable before sending it to the sql server

Les choses ne sont pas toujours comme elles paraissent!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-22 : 23:33:50

whats format in which you get datevalue from datepicker?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

koutei
Starting Member

7 Posts

Posted - 2012-03-22 : 23:41:52

i don't really know. But from the differents erro messages i got i know for sure it is in varchar datatype. i want to convert it to date or datetime(at last) to be able to store it!

Les choses ne sont pas toujours comme elles paraissent!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-22 : 23:45:56

try printing it out first

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

koutei
Starting Member

7 Posts

Posted - 2012-03-23 : 00:00:33

Either a date variable or a the dtpicker value i can't print it out! Sorry. It said it cannot convert it

Les choses ne sont pas toujours comme elles paraissent!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-23 : 00:15:34

hmm...that doesnt make sense...you should be able to print it as varchar

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

koutei
Starting Member

7 Posts

Posted - 2012-03-23 : 00:30:53

I finally got how to print it! "23-03-2012 09:58:56" Now do you know how to change the format, make it like "YYYY-MM-DD" or "YYYY-MM-DD HH:MM:SS" like u said in your blog.

Les choses ne sont pas toujours comme elles paraissent!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-23 : 00:45:26

try


cmd6 = conn.CreateCommand
cmd6.CommandText = "INSERT INTO Employees_Details (EmpID, FirstName, Surname, Gender, DateOfBirth, DateOfJoin, PANNumber, Address, PhoneNoHome, PhoneNoMob, Department, CivilStatus, Qualification, Title) 
VALUES('" & Trim(txtEmpID.Text) & "','" & Trim(txtFirstName.Text) & "','" & Trim(txtSurname.Text) & "','" & Trim(cboGender.Text) & "',CONVERT(datetime,'" & Trim(dtpDateOfBirth.Value) & "',103),CONVERT(datetime,'" & Trim(dtpDateOfJoin.Value) & "',103),
'" & Trim(txtPANNumber.Text) & "','" & Trim(txtAddress.Text) & "','" & Trim(txtPhoneHome.Text) & "','" & Trim(txtPhoneMob.Text) & "',
'" & Trim(cboDepartment.Text) & "','" & Trim(cboCivilStatus.Text) & "','" & Trim(txtQualification.Text) & "','" & Trim(cboTitle.Text) & "')"
check = cmd6.ExecuteReader.RecordsAffected()
If check > 0 Then
MessageBox.Show("The Record Was Saved Successfully!", "Successfull Registration", MessageBoxButtons.OK, MessageBoxIcon.Information)
conn.Close()

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

koutei
Starting Member

7 Posts

Posted - 2012-03-23 : 01:05:31

thnak you but i got this error message <<Error: .Net SqlClient Data Provider:Incorrect syntax near '1021324587'.>>

Les choses ne sont pas toujours comme elles paraissent!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources