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 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.