about the type field : datetime and smalldatetime

hi all,
the field type :datetime and smalldatetime, i still can't understand.
everytime when i inserted the data to the db, i also get the error message"System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
i must change the field type tostring, so that i can insert data
my code
txt_datetime.text = '5/2/2006'
insert into datetime (datetime) values ('"& txt_datetime.text & "')"

can anybody tell me the reason?

thank you!!In your SQL statement:

insert into datetime (datetime) values ('"& txt_datetime.text & "')"

Is your table named datetime? Isn't that a keyword? If that's not the problem, then can you post the source code where you are inserting the value?|||

What culture is your SQL Server running under?

I'm guessing this statement will fail as well:

SELECT CAST('5/2/2006' as datetime)

Try running that as a query.

But beyond that... Stop generating SQL Statements by using string concatenation and your problem will go away.

dim conn as new sqlconnection("{Connection string}")
conn.open
dim cmd as new sqlcommand("INSERT INTO [datetime](datetime) values (@.datetime)",conn)
cmd.parameters.add("@.datetime",sqldbtype.datetime).value='5/2/2006'
cmd.executenonquery
conn.close

|||

hi all,

first at all, thank you for reply
now i solved the problem and i think i know the reason, because the sql server and .net software are in difference language , sql server is tradition chinese version and .net software is in english version . the field type - datetime in sql server (english version ) is "dd/mm/yyyy" but in chinese is "mm/dd/yyyy" and when i insert the data to the db via the .net application, the datetime format is"dd/mm/yyyy", so i have to splite the datatime string and change it to "mm/dd/yyyy".

hijcasp,
my table name is called paper and the field name is deadline.
txt_deadline.text = '5/2/2006'
dim deadline_array = splite(txt_deadline.text, "/")
dim deadline as string
deadline = deadline_array.(1) & "/" & deadline_array(0) & "/" & deadline_array(2)
"insert into paper(deadline) values(" & deadline & ")"

hi,Motley
"Stop generating SQL Statements by using string concatenation ", why i should stop to use concatenation??

|||

1) Because you run into problems like the one you mentioned.

2) Because string concatenating sql strings will suffer from SQL Injection attacks if you aren't really careful.

3) Performance.

4) Portability.

5) Maintanability.

6) Readability.

If at a later time you decide to change the SQL Server culture, your code breaks. If you decide to change your .NET culture, your code breaks. If you decide you want to localize your application to multiple cultures, your code breaks. The code I gave you runs no matter what culture your .NET appliction is, or what culture your SQL Server is, and runs faster and demands less resources of the SQL Server making it more scalable.

Just as a point of clarification, the issue wasn't that the language was different, it's that the culture formats of the .NET application and the SQL Server were different. The date format for en-US (English in the United States) is mm/dd/yy. The date format for en-GB (English in Great Britian) is dd/mm/yy. You had the right idea on the cause, just not the correct term. Cultures are made up of a language and a locale. The language part of the culture wasn't the problem, it's the locale part that determines what format dates, times, numbers, currency are in, and that's the part that caused you the problem.