SUMMARY: I don't know which VBA data type (Date, String or ?) to store the VBA date-in that will be passed to SQL Server for insert via the ADO Parameter definition. When the ADO JDate parameter is established it asks for a data type. I used adDate but have problems.
My VB/ADO db connection is working, I can pass text based SQL for inserts and reads with success. However for various reasons I need to switch an INSERT query to using ADO parameters vs just text based SQL.
For testing, I simplified all of this to a single table, with 2 simple columns for testing.
SQL Server table: testJournal - has 2 columns: City nchar(100) not null and JDate date not null. I started testing the code below with the same table name containing only the City column. It worked perfectly, aka each run inserted a row in SQL Server. Once I manually drop and recreate the same table to include JDate and run the VBA code to insert, I get an error that a Null cannot be inserted into JDate. aka The VBA date I am providing to the ADO parameter is never making it to SQL Server via the ADO parameter.
Some of my VBA Code:
Dim TDate As Date
TDate = "2025-03-07"
Call mDataBase.Open("Provider=SQLOLEDB;Initial Catalog=" & pCatalog & ";Data Source=xxxxxxxxxxxxxxxx" & pServer & ";Integrated Security=SSPI")
mCmd.ActiveConnection = mDataBase
With mCmd
.Parameters.Append .CreateParameter("City", adLongVarChar, adParamInput, 100, "Beaumont")
.Parameters.Append .CreateParameter("JDate", adDate, adParamInput, TDate)
End With
.CommandText = "INSERT INTO testJournal ( City, JDate ) VALUES(?,?)"
Set mRS = mCmd.Execute
When the last line above runs I receive an error that SQL Server column JDate does not allow nulls. I inspected mCmd on the VB side during runtime. The value property for "City" is "Beaumont". However, the value property for "JDate" is null.
What combination of VBA variable and/or VBA ADO Parameter data-type do I use to successfully pass my VBA date into a SQL Server Date column?
You are missing the size parameter for the date line. While it is optional, you would have to specify the arguments by name if you want to leave it out. If you specify them by order, like you are doing, then you need to specify something for it.
So because you are passing arguments by order, you are passing the date in the size parameter. I have no idea how that is working, maybe it is converting that to an integer somehow?
You see the 100 in your prior line. You need the equivalent for the second line, or to specify the arguments by name rather than by position.
The type should likely be adDBDate or adDBTimeStamp.
When I was using it I specified a size of 8. I'm not sure a valid value is required there. You might try 0 and see if it works. It might ignore that for certain types.
Like I said in the comment, you could also specify it as a string type and let sql convert it for you. But you would need to format the date in yyyy-mm-dd format so sql server will always interpret it correctly regardless of locale.