By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,080 Members | 1,845 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,080 IT Pros & Developers. It's quick & easy.

Parameter Query

P: 1
I keep getting an conversion error when the open statement executes in following code.

I can hard code the dates in the where clause and the code works fine. Whenever I try to pass the dates as a parameter, I get the conversion error message.

I'm trying to accept date input from a form in Excel 2007.

The query needs to access a SQL server db and select the records that exist within the date range.

I am selecting against a view, not sure if that makes any difference. It should not as I tested the query in SQL Server management studio.

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnCancel_Click()
  2.     Unload Me
  3. End Sub
  4.  
  5. Private Sub btnOk_Click()
  6. ' Create a connection object.
  7. Dim cnPubs As ADODB.Connection
  8. Set cnPubs = New ADODB.Connection
  9.  
  10. ' Provide the connection string.
  11. Dim strConn As String
  12.  
  13. 'Use the SQL Server OLE DB Provider.
  14. strConn = "PROVIDER=SQLOLEDB;"
  15.  
  16. 'Connect to the Pubs database on the local server.
  17. strConn = strConn & "DATA SOURCE=HDQTMAPSMIA02V;INITIAL CATALOG=config_f9;"
  18.  
  19. 'Use an integrated login.
  20. strConn = strConn & " INTEGRATED SECURITY=sspi;"
  21.  
  22. 'Now open the connection.
  23. cnPubs.Open strConn
  24.  
  25.     Sheet1.Range("D4").Value = dtpPayPeriodBeginDt.Value
  26.     Sheet1.Range("D6").Value = dtpPayPeriodEndDt.Value
  27.  
  28. ' Create a recordset object.
  29. Dim rsPubs As ADODB.Recordset
  30. Set rsPubs = New ADODB.Recordset
  31.  
  32. Dim parm1 As ADODB.Parameter
  33. Dim parm2 As ADODB.Parameter
  34.  
  35. Set parm1 = New ADODB.Parameter
  36. Set parm2 = New ADODB.Parameter
  37.  
  38. Dim strBeginDt As String
  39. Dim strEndDt As String
  40.  
  41. strBeginDt = dtpPayPeriodBeginDt.Value
  42. strEndDt = dtpPayPeriodEndDt.Value
  43.  
  44. With rsPubs
  45.     ' Assign the Connection object.
  46.     .ActiveConnection = cnPubs
  47.     ' Extract the required records.
  48.     .Open "select extra_code_1, surname, forenames, posting_sdate, posting_edate,grade_sht_title from dbo.ncl_active_deck_crew where posting_sdate <= 'convert(datetime, strBeginDt)' and posting_edate >= 'convert(datetime, strEndDt)' order by grade_sht_title,surname,forenames"
  49. ' Copy the records into cell A1 on Sheet1.
  50.     Sheet1.Range("b20:g1048576").Clear
  51.     Sheet1.Range("B20").CopyFromRecordset rsPubs
  52.  
  53.     ' Tidy up
  54.     .Close
  55. End With
  56.  
  57. cnPubs.Close
  58. Set rsPubs = Nothing
  59. Set cnPubs = Nothing
  60.  
  61.     Unload Me
  62. End Sub
Dec 29 '11 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,494
Dave:
I keep getting an conversion error when the open statement executes in following code.
If you give us a line number then we can tell which of the two 'the open statements' you are referring to.

If it's the connection string that's a problem then I can't help I don't think, but if it's line #48 with the SQL string then I may be able to.
Dec 29 '11 #2

Expert 100+
P: 446
Hi Dave,
I looked at this this-morning but could not understand where the SQL was executing. I understand you reading Start and End dates from a spreadsheet, using them as parameters in a query in a SQL Server database then copying the data back into the spreadsheet (starting at cell B20 not A1 as indicated by the comment)?

Are you using Access VBA? The 'Unload Me' at line #2 sounds a bit like a VB form?, so you may be in the wrong forum for the best help.

One bit I am suspicious of is "strBeginDt = dtpPayPeriodBeginDt.Value", storing the date as a string. Personally I would try keeping it as a Date. I know you are trying to convert it to a DateTime in your SQL statement. But to my knowledge 'convert' is a SQL Server function (not Access)in which case the parameter would have to have been declared with an '@' prefix.

But the fact that you are trying to run against an ADO recordset indicates the SQL may be running against a JET database, in which case NeoPa is right and he can help with the correct syntax for your country.
S7
Dec 29 '11 #3

NeoPa
Expert Mod 15k+
P: 31,494
S7:
But the fact that you are trying to run against an ADO recordset indicates the SQL may be running against a JET database
It's actually DAO (Data Access Objects), rather than ADO or ADODB (ActiveX Data Objects Database), which would indicate a Jet reference. Actually, either can work with Jet, but DAO is certainly more of a native protocol for it. It's not hard to see why these two are often confused for each other TBF.
Dec 29 '11 #4

Expert 100+
P: 446
NeoPa,
I am not even sure that Mr. Wysocki is using Access, as I indicated in my post. He is definitely using ADODB which is indicative but I believe not necessarily means JET. Until this is clear we cannot be certain which syntax to use.
S7
Dec 30 '11 #5

NeoPa
Expert Mod 15k+
P: 31,494
Sierra7:
... ADODB which is indicative but I believe not necessarily means JET
I don't believe that's right S7. Jet is one of many database engines that ADODB supports, as it's more 'general purpose' than DAO, which, while it can be used elsewhere, is designed to support Jet specifically.

IE. DAO could be taken to imply Jet, but ADODB is less indicative of anything specific.

In this case lines #13 & #14 indicate the engine in use is SQL Server. This would indicate T-SQL syntax rather than Jet SQL. I'm somewhat familiar with the former, but less so than the latter obviously.

PS. It seems the application is not Access, but Excel 2007. I'm hoping that the VBA syntax and object set available is similar to that of Access.
Dec 30 '11 #6

Expert 100+
P: 446
NeoPa,
I totally agree and it came blindingly obvious when I started to swot-up on ADO ConnectionStrings. Nice observation that the application is run from within Excel though!

Dave,
Sorry to keep you waiting while NeoPa and I have been confiring amongst ourselves! On this forum we usually work in Access and although a few years ago Microsoft made an attempt to direct us down the ADO route most of us stuck with DAO. Frankly I have had more exposure to Polish than I have to ADO.

However, the code that you posted looks like it has been modelled on a Microsoft example (by virtue of it's reference to the Pubs database), so it would not be defining parameters without reason (Lines #32 & 33). Normally the problem is how to pass a parameter into a SQL command, so I suggest the following. Set the two parameters to the start and end dates then include the parameter variables in the sQL statement thus;
Expand|Select|Wrap|Line Numbers
  1. Set parm1 = New ADODB.Parameter 
  2. Set parm2 = New ADODB.Parameter 
  3.  
  4. 'Dim strBeginDt As String 
  5. 'Dim strEndDt As String 
  6.  
  7. param1 = dtpPayPeriodBeginDt.Value 
  8. param2 = dtpPayPeriodEndDt.Value 
  9.  
  10. With rsPubs 
  11.     ' Assign the Connection object. 
  12.     .ActiveConnection = cnPubs 
  13.     ' Extract the required records. 
  14.     .Open "select extra_code_1, surname, forenames, posting_sdate, posting_edate,grade_sht_title from dbo.ncl_active_deck_crew where posting_sdate <= 'convert(datetime, param1)' and posting_edate >= 'convert(datetime, param2)' order by grade_sht_title,surname,forenames" 
  15. ' Copy the records into cell A1 on Sheet1. 
  16.     Sheet1.Range("b20:g1048576").Clear 
  17.     Sheet1.Range("B20").CopyFromRecordset rsPubs 
  18.  
  19.     ' Tidy up 
  20.     .Close 
  21. End With 
  22.  
This is what you would do in Transact SQL which runs on the server. I have been trying to make time to test this before posting but have not been able to, so cannot guarantee it will work.

You will see that I have not made use of the two string variables. It may be necessary to write the values to strings then set the parameters equal to the strings, I don't know.

If still not working I would try removing the quotes around the Convert() functions.

Gook luck. I will try again next week.
S7
Dec 31 '11 #7

NeoPa
Expert Mod 15k+
P: 31,494
This is all much easier when the actual question is clear of course, but it's understandable that the OP might be away over the Christmas period, so my first question (post #2) still not being answered is no big deal.

However, if we assume for now that the problem is with the 2nd occurrence of the Open command in the posted code, then we are looking at the SQL string which is created by line #48 of the posted code. If that is the case then we are looking at a SQL problem so you (Dave) need to look first at When Posting (VBA or SQL) Code. I suspect when you actually look at the SQL string the fact that the T-SQL Convert calls are there as literal strings instead of converted values will ring bells and indicate where you need to focus your attention.
Dec 31 '11 #8

Post your reply

Sign in to post your reply or Sign up for a free account.