CODEWITHSUNDEEP
sql-serverexcelvba
TSQL_Newbie
TSQL_Newbie

Reputation: 821

Insert date from cell into sql table - Defaults to 1900-01-01

I can update an SQL table via Excel VBA except the date. The value comes through as 1900-01-01 or in some cases where I have played with the format it is shown as 1900-01-28.

It is a simple setup just for testing.
One Table with two columns CellText and CellDate, both getting their values from a cell range.

The value expected for CellText is 'Some Text'
The value expected for CellDate is 24/03/2015

Sub UpdateTable()

Dim cnn As ADODB.Connection
Dim uSQL As String
Dim strText As String
Dim strDate As Date

strText = ActiveSheet.Range("b4").Value
strDate = Format(ActiveSheet.Range("c4").Value, "dd/mm/yyyy")

Set cnn = New Connection
cnnstr = "Provider=SQLOLEDB; " & _
            "Data Source=ServerName; " & _
            "Initial Catalog=DbName;" & _
            "User ID=UserName;" & _
            "Trusted_Connection=Yes;"

cnn.Open cnnstr

uSQL = "INSERT INTO tbl_ExcelUpdate (CellText,CellDate) VALUES ('" & strText & "', " & strDate & ")"

Debug.Print uSQL

cnn.Execute uSQL
cnn.Close
Set cnn = Nothing
Exit Sub
End Sub

My Debug value is

INSERT INTO tbl_ExcelUpdate (CellText,CellDate) VALUES ('Some Text ', 24/03/2015)

My CellDate format in table is datetime.

Upvotes: 1

Views: 10010

Answers (3)

Nikolaos Polygenis
Nikolaos Polygenis

Reputation: 691

you have to correct the strDate = Format(ActiveSheet.Range("c4").Value, "dd/mm/yyyy") to

strDate = Format(ActiveSheet.Range("c4").Value, "dd-mm-yyyy").

Then you have to put quotes:

uSQL = "INSERT INTO tbl_ExcelUpdate (CellText,CellDate) VALUES ('" & strText & "', '" & strDate & "')"

Upvotes: 0

Dbloch
Dbloch

Reputation: 2376

Looks like you are missing single quotes on either side of the date.

INSERT INTO tbl_ExcelUpdate (CellText,CellDate) VALUES ('Some Text ', 24/03/2015)

Should be 

INSERT INTO tbl_ExcelUpdate (CellText,CellDate) VALUES ('Some Text ', '24/03/2015')

Upvotes: 1

Rajnikant Sharma
Rajnikant Sharma

Reputation: 606

Use CDate function to convert string formated date to date type

Sub UpdateTable()

Dim cnn As ADODB.Connection
Dim uSQL As String
Dim strText As String
Dim strDate As Date

strText = ActiveSheet.Range("b4").Value
strDate = Format(ActiveSheet.Range("c4").Value, "dd/mm/yyyy")

Set cnn = New Connection
cnnstr = "Provider=SQLOLEDB; " & _
        "Data Source=ServerName; " & _
        "Initial Catalog=DbName;" & _
        "User ID=UserName;" & _
        "Trusted_Connection=Yes;"


cnn.Open cnnstr     uSQL = "INSERT INTO tbl_ExcelUpdate (CellText,CellDate) VALUES ('" & strText & "', " & CDate(strDate) & ")"

Debug.Print uSQL

cnn.Execute uSQL
cnn.Close
Set cnn = Nothing
Exit Sub
End Sub

Upvotes: 0

Related Questions