admin管理员组

文章数量:1410697

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?

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?

Share Improve this question edited Mar 7 at 21:03 Thom A 96.3k11 gold badges61 silver badges95 bronze badges asked Mar 7 at 20:44 Frank ZappaFrank Zappa 4532 gold badges11 silver badges23 bronze badges 3
  • I think ADODB predated the sql server date type. So you might need to use datetime on the sql server side. The vba side looks okay but I always specified the length. – Will Rickards Commented Mar 7 at 21:14
  • 1 You also might be able to specify the type as one of the string types (adVarChar, adVarWChar) and let sql server do the conversion for you. If you do that though, I would pass it yyyy-mm-dd. – Will Rickards Commented Mar 7 at 21:21
  • @WillRickards thank you for the quick reply. Your second suggestion worked. aka I made the VBA variable a string, assigned the date in quotes, changed the VBA ADO Parameter type to adVarChar 100. The row was inserted. Thanks again. – Frank Zappa Commented Mar 7 at 21:46
Add a comment  | 

1 Answer 1

Reset to default 1

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.

本文标签: VBA ADO INSERT INTO SQL ServeradDate not passing dateStack Overflow