Error converting datetime

Hello,

I use a VB form (datagrid) to enter data in a SQL table. When I insert the record, the following error occurs:
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string.

The field in the SQL table is defined as datetime.
This is how I update the SQL table:

Private Sub UpdateRecordset()
'Update recordset with MsFlexgrid
Dim SQLString As String
Dim rij As Integer

'First delete all data
SQLString = "Delete From [Orders - sub verkoop] WHERE OrderID = " & frmInterneOrders.LblOrdernummer
MyConnection.Execute SQLString

'Add data
For rij = 1 To MsFlexgrid1.Rows - 2

SQLString = "INSERT INTO [Orders - sub verkoop] (OrderID, Volgnummer, Gereed, [Datum bij klant], Aantal, opmerking ) " _
& "SELECT " & frmInterneOrders.LblOrdernummer & " AS ORDNR, " _
& MsFlexgrid1.TextMatrix(rij, 0) & " AS VOLG, " _
& "'#" & Format(MsFlexgrid1.TextMatrix(rij, 1), "dd/MM/yyyy") & "#' AS GER, " _
& "'#" & Format(MsFlexgrid1.TextMatrix(rij, 2), "dd/MM/yyyy") & "#' AS DATKLANT, " _
& MsFlexgrid1.TextMatrix(rij, 3) & " AS QNTY, " _
& "'" & MsFlexgrid1.TextMatrix(rij, 4) & "' AS OPM"

MyConnection.Execute SQLString 'Do it
Next rij


End Sub


Patrick.

Use the CAST or CONVERT function on the data being inserted.


CAST('5/20/2003' AS datetime)

--OR

CONVERT(datetime, '5/20/2003')

Use the CAST or CONVERT function on the data being inserted.


CAST('5/20/2003' AS datetime)

--OR

CONVERT(datetime, '5/20/2003')



When I use convert:

SQLString = "INSERT INTO [Orders - sub verkoop] (OrderID, Volgnummer, Gereed, [Datum bij klant], Aantal, opmerking ) " _
& "SELECT " & frmInterneOrders.LblOrdernummer & " AS ORDNR, " _
& MsFlexgrid1.TextMatrix(rij, 0) & " AS VOLG, " _
& "CONVERT(datetime, '" & MsFlexgrid1.TextMatrix(rij, 1) & "') AS GER, " _
& "CONVERT(datetime, '" & MsFlexgrid1.TextMatrix(rij, 2) & "') AS DATKLANT, " _
& MsFlexgrid1.TextMatrix(rij, 3) & " AS QNTY, " _
& "'" & MsFlexgrid1.TextMatrix(rij, 4) & "' AS OPM"


I get this error:
[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char daya type to a datetime data type resulted in an out-of-range datetime value.

When I debug the SQLstring, it looks ok:

INSERT INTO [Orders - sub verkoop] (OrderID, Volgnummer, Gereed, [Datum bij klant], Aantal, opmerking ) SELECT 1833 AS ORDNR, 1 AS VOLG, CONVERT(datetime, '22-05-03') AS GER, CONVERT(datetime, '29-05-03') AS DATKLANT, 1250 AS QNTY, 'test' AS OPM



When I use CAST:


SQLString = "INSERT INTO [Orders - sub verkoop] (OrderID, Volgnummer, Gereed, [Datum bij klant], Aantal, opmerking ) " _
& "SELECT " & frmInterneOrders.LblOrdernummer & " AS ORDNR, " _
& MsFlexgrid1.TextMatrix(rij, 0) & " AS VOLG, " _
& "CAST('" & MsFlexgrid1.TextMatrix(rij, 1) & "' AS datetime) AS GER, " _
& "CAST('" & MsFlexgrid1.TextMatrix(rij, 2) & "' AS datetime) AS DATKLANT, " _
& MsFlexgrid1.TextMatrix(rij, 3) & " AS QNTY, " _
& "'" & MsFlexgrid1.TextMatrix(rij, 4) & "' AS OPM"


I get the same error.
The debugged string also looks ok.


INSERT INTO [Orders - sub verkoop] (OrderID, Volgnummer, Gereed, [Datum bij klant], Aantal, opmerking ) SELECT 1833 AS ORDNR, 1 AS VOLG, CAST('22-05-03' AS datetime) AS GER, CAST('29-05-03' AS datetime) AS DATKLANT, 1250 AS QNTY, 'TEST CAST' AS OPM


PATRICK

HELP....

I'm desperate.
I cannot continue my programme.

Please help me.

Patrick

GOT IT:


SQLString = "INSERT INTO [Orders - sub verkoop] (OrderID, Volgnummer, Gereed, [Datum bij klant], Aantal, opmerking ) " _
& "SELECT " & frmInterneOrders.LblOrdernummer & " AS ORDNR, " _
& MsFlexgrid1.TextMatrix(rij, 0) & " AS VOLG, " _
& "'" & Format(MsFlexgrid1.TextMatrix(rij, 1), "MM/dd/yy hh:mm:ss") & "' AS GER, " _
& "'" & Format(MsFlexgrid1.TextMatrix(rij, 2), "MM/dd/yy hh:mm:ss") & "' AS DATKLANT, " _
& MsFlexgrid1.TextMatrix(rij, 3) & " AS QNTY, " _
& "'" & MsFlexgrid1.TextMatrix(rij, 4) & "' AS OPM"


I have tried about everything but this seems to work.
I don't know why but it does.


Patrick.