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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
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.
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.
You can download TodoTxtTestFirst.zip
