Problem importing Date Field into SQL DB.

[Kevin Hodgson]

I'm trying to import from a text file into a Sql DB, but I'm getting hung up
on a date field.

My text file has the date in the dd/mm/yyyy format.
I Cast that field to CDate in VB.NET, which sets it to #12/31/2005#

On my Insert, I get a SQL error from the .NET SqlClient Data provider:
"Syntax Error converting datetime from character string"

I tried not casting it as Date, which passed it into the parameter as
"2005-12-31" but that gave me the same error.

Does anyone have any ideas?

 

[Guest]

Basically. If you use CDate, The sting should meet
System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern. Otherwise you should use Convert.ToDateTime(String, IFormatProvider).


If you use string to insert date column. The string should persist with SQL
server datatime format set from control panel-->Regional Options

 

[Cor Ligthert]

Kevin,

I made a sample for you, maybe you can try if it does the job for you.

\\\
Public Class Main
Public Shared Sub Main()
Dim Conn As New SqlClient.SqlConnection _
("Server=localhost;DataBase=Northwind;Integrated Security=SSPI")
Try
Threading.Thread.CurrentThread.CurrentCulture = _
New Globalization.CultureInfo("en-US")
'The above only because my system setting is nl-NL

Dim strSQL As String = "INSERT INTO Employees " & _
"(LastName, FirstName, HireDate)" & _
"VALUES ('Kevin', 'Hodgson', @HireDate)"
Dim cmd As New SqlClient.SqlCommand(strSQL, Conn)
Dim myparam As New SqlClient.SqlParameter
myparam.DbType = DbType.DateTime
myparam.ParameterName = "@HireDate"
myparam.Value = CDate("12-31-2005")
cmd.Parameters.Add(myparam)
Conn.Open()
cmd.ExecuteNonQuery()
Catch ex As SqlClient.SqlException
MessageBox.Show(Ex.ToString)
Catch ex As Exception
MessageBox.Show(Ex.ToString)
End Try
End Sub
End Class
///

I hope it helps?

Cor

 

[Cor Ligthert]

Kevin,

Extra, that setting of the globalization you should try to avoid, it is
only for this sample because I assume because of your messagedate you are in
the US.

Normally better would be this in the sample.
myparam.Value = New Date(2005, 12, 31)

Which goes in all globalization settings, however is a very academical
sample. Normally the user will enter the values in the globalization he is
used and is than the setting of globalization not needed because than
converting using CDate is done by the framework.

Cor