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) & ")"
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
INSERT INTO dbo.TestDateBigInt
( c1 )
VALUES ( @BigInt -- c1 - bigint
Then I wrote some code to insert rows
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
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
sSql = "INSERT INTO TestDateBigInt (c1) VALUES (" & vaData(1, 1) & ")"
'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
On Error Resume Next
Set rs = cn.Execute("SELECT * FROM TestDateBigInt")
Set rs = Nothing
Set cmd = Nothing
Set cn = Nothing
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.
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.