Thom
12-10-2008, 07:36 AM
Hi all.
A database is being tinkered with and we needed a few columns and the data.
So I saved it down to Access and then Excel.
three columns
DateEntered
Date_Expiry
PhotoURL
first two are a datetime format in sql.
third is a varchar.
So now I need to write this back into a database. I have setup a blank db to test on. This is my code:
Dim row As Integer
Dim strSQL As String
For row = 2 To 3382
strSQL = "insert into tblTest (DateEntered, DateExpiry, PhotoURL) values ('" & XLSheet.Cells(row, 3).Value & "','" & XLSheet.Cells(row, 4).Value & "','" & XLSheet.Cells(row, 5).Value & "')"
'MsgBox (strSQL)
con.Execute strSQL
Next row
If I set the three columns in SQL as varchar or char, it works fine.
But I need the dates in datetime format. They are formatted correctly in Excel (eg: 01/02/2008)
This is the error I'm getting:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
So I tried without quote marks in the vba but I get a syntax error.
Ideas?
Thanks :hi:
update -
Actually I'll explain a little more as I've noticed a few things.
Not all the fields have a datestamp on them or a PhotoURL.
It seems to error at line 7. I thought it might be to do with NULLs but that isn't the case. In fact, in my SQL, where the column should be NULL or blank, it has the date - 01/01/1900 in it!
So I don't want that, but I am still getting the above error, even though it is entering the data
A database is being tinkered with and we needed a few columns and the data.
So I saved it down to Access and then Excel.
three columns
DateEntered
Date_Expiry
PhotoURL
first two are a datetime format in sql.
third is a varchar.
So now I need to write this back into a database. I have setup a blank db to test on. This is my code:
Dim row As Integer
Dim strSQL As String
For row = 2 To 3382
strSQL = "insert into tblTest (DateEntered, DateExpiry, PhotoURL) values ('" & XLSheet.Cells(row, 3).Value & "','" & XLSheet.Cells(row, 4).Value & "','" & XLSheet.Cells(row, 5).Value & "')"
'MsgBox (strSQL)
con.Execute strSQL
Next row
If I set the three columns in SQL as varchar or char, it works fine.
But I need the dates in datetime format. They are formatted correctly in Excel (eg: 01/02/2008)
This is the error I'm getting:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
So I tried without quote marks in the vba but I get a syntax error.
Ideas?
Thanks :hi:
update -
Actually I'll explain a little more as I've noticed a few things.
Not all the fields have a datestamp on them or a PhotoURL.
It seems to error at line 7. I thought it might be to do with NULLs but that isn't the case. In fact, in my SQL, where the column should be NULL or blank, it has the date - 01/01/1900 in it!
So I don't want that, but I am still getting the above error, even though it is entering the data