CODEWITHSUNDEEP
sqlexcelvba
MBt93
MBt93

Reputation: 17

VBA for upload to SQL from Excel stopped working?

So, I frankensteined a bit of code together last week, to upload data from my excel sheet to my database. It worked perfectly. I've come back to it today, and now it's not working... I keep getting the error message:

"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

I think the fact that it WAS working has left me a bit blind to find the problem... So I'd be really grateful if someone with fresh eyes could have a look over it, and see if they can see the problem!

Sub Button1_Click()

    If MsgBox("Are you sure you want to upload this data?", vbYesNo) = vbNo Then Exit Sub

    Dim conn As New ADODB.Connection
    Dim iRowNo, sArtistID, sSaleMonth, sSaleYear As Integer
    Dim sValue As Long
    Dim sSaleroom As String
    Dim sUploaded As Date

    sUploaded = Now()

    With Sheets("DataUpload")

        'Open a connection to SQL Server

        'Skip the header row
        iRowNo = 2

        'Loop until empty cell in CustomerId
        Do Until .Cells(iRowNo, 1) = ""
            sArtistID = .Cells(iRowNo, 1)
            sSaleMonth = .Cells(iRowNo, 2)
            sSaleYear = .Cells(iRowNo, 3)
            sSaleroom = .Cells(iRowNo, 4)
            sValue = .Cells(iRowNo, 5)

            'Generate and execute sql statement to import the excel rows to SQL Server table
            conn.Execute "insert into dbo.Data (ArtistID, SaleMonth, SaleYear, SaleRoom, Value, Uploaded) values ('" & sArtistID & "', '" & sSaleMonth & "', '" & sSaleYear & "', '" & sSaleroom & "', '" & sValue & "', '" & sUploaded & "')"

            iRowNo = iRowNo + 1
        Loop

        MsgBox "Data imported."

        conn.Close
        Set conn = Nothing

    End With


End Sub

My Database:

DataID (int) has Identity Specification on, increment 1. ArtistID, salemonth/year and value are all int, saleroom is char and uploaded is datetime.

Thanks very much!

Upvotes: 0

Views: 130

Answers (1)

zaptask
zaptask

Reputation: 707

We should look for the problem in the description.

"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

For example you might be using a European date convention dd/mm/yyyy and SQL server is expecting American mm/dd/yyyy. Today we have 14th of March. In that case on Friday we had 11th of March which could be interpreted as 3rd of November. There is no 14th month so you get an error today and it was fine 3 days ago.

Before uploading I do

o_adodb_connection.Execute ("set dateformat dmy")

so that the SQL knows what to expect (SQL Server in my case - I do not know what you use).

Upvotes: 1

Related Questions