The Daily Dose

I take data that has been entered in Excel and I store it in SQL Server. A lot. I do that a lot. The proper way to do that is to create a stored procedure for every database operation you need and to execute that stored procedure from VBA. The quick and dirty way is to build a SQL string and execute it. As you might have guessed from the title, I chose the quick and dirty way and was recently bit in the ass.

Here’s the long and the short of it: Some numbers got formatted as dates and it really screwed stuff up. I had some code that looked similar to

vaData = lo.DataBodyRange.Value
sSql = "INSERT INTO Blend (ManifestID) VALUES (" & vaData(i,1) & ")"
adConn.Execute sSql

The field ManifestID is a BIGINT and vaData(i,1) contained 4/15/2023. The ManifestID was 45031, someone (me) mistook that for a date that lost its formatting and promptly fixed (broke) the formatting. I noticed that several dozen entries in Blend had a ManifestID of zero. SQL Server dutifully took 4/15/2023, did the division (4 divided by 15 divided by 2,023), came up with zero, and put zero in the field.

After some self-flagellation, I wondered if a stored procedure would have caught this error. I assumed that when I tried to pass a date into a BIGINT parameter, the code would error out and I would have avoided this whole mess. But I was wrong. Instead, the stored procedure converted the date to its integer value – not by dividing like in the SQL String method, but by some conversion that I didn’t think was possible. Excel stores dates as the number of days since 12/31/1899. That’s not unique, but I’m pretty sure SQL server doesn’t store them that way. And how would ADO or T-SQL know to convert it in that way?

I devised a test. First create a table

CREATE TABLE [dbo].[TestDateBigInt](
	[c1] [BIGINT] NULL

Next, create a stored procedure to insert records

CREATE PROCEDURE [dbo].[spTestDateBigInt] @BigInt AS BIGINT
AS
    INSERT  INTO dbo.TestDateBigInt
            ( c1 )
    VALUES  ( @BigInt  -- c1 - bigint
              )

Then I wrote some code to insert rows

Sub TestDateBigInt()
    
    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim pm As ADODB.Parameter
    Dim rs As ADODB.Recordset
    Dim sSql As String
    Dim i As Long
    Dim vaFormats As Variant
    Dim vaData As Variant
    
    On Error GoTo ErrH
    
    Set cn = New ADODB.Connection
    cn.Open msCONN
    
    vaFormats = Split("General m/d/yyyy")
    
    For i = 0 To 1
        'Change the format
        Sheet1.Range("G1").NumberFormat = vaFormats(i)
        vaData = Sheet1.Range("G1:G2").Value
        'Insert record
        sSql = "INSERT INTO TestDateBigInt (c1) VALUES (" & vaData(1, 1) & ")"
        cn.Execute sSql
        
        'Insert record via stored procedure
        Set cmd = New ADODB.Command
        cmd.ActiveConnection = cn
        cmd.CommandText = "spTestDateBigInt"
        cmd.CommandType = adCmdStoredProc
        Set pm = cmd.CreateParameter("@BigInt", adBigInt, adParamInput)
        pm.Value = Sheet1.Range("G1").Value
        cmd.Parameters.Append pm
        
        cmd.Execute
    Next i
    
ErrH:
    On Error Resume Next
        Set rs = cn.Execute("SELECT * FROM TestDateBigInt")
        Debug.Print rs.GetString
        rs.Close
        cn.Close
        Set rs = Nothing
        Set cmd = Nothing
        Set cn = Nothing
    
End Sub

In the code, I define two formats in an array: General and m/d/yyyy. I loop through that array and apply the formats to cell G1 where I have an unsuspecting integer. In the first pass, it’s formatted as General and looks like a proper integer. I build up a INSERT INTO Sql string and execute it right off the connection. Then, still inside the loop, I do it the right way: Create a command object, add a parameter, and execute it.

In the second iteration of the loop, cell G1 gets formatted as a date and it all happens again.

I was expecting an error, so I had an error handler that printed out the whole table whenever thing bombed. But it never bombed. It executed just fine.

45000
45000
0
45000

With the integer formatted as a number, both the string method and the stored procedure method inserted properly. That’s the first two 45000’s. The third 45000 is the string method when the integer is formatted as a date. That’s the one where SQL does division. The last 45000 is the one I thought would error out. But passing in a date to a BIGINT parameter converted it to the proper number. I even put G1 into a variant array to simulate my real world situation.

I’m still don’t know, and am interested to know, what is doing the conversion. But in the meantime I’m happy to learn my lesson and vow to use stored procedures like a good boy.