0

good afternoon

i'm going crazy with passing parameters to a very simple query with a subquery; i wrote hundreds of complicated queries, i'm afraid my fault is just in passing parameters to the subquery or everything else seems very normally manageable

making things simpler i prepare my query this way:

Set myConn = Server.CreateObject("ADODB.Connection")
myConn.Mode = 3 '3 = adModeReadWrite
myConn.Open MM_DEFDB_STRING
Set myCmd = Server.CreateObject("ADODB.Command")
myCmd.ActiveConnection = myConn
myCmd.CommandType = 1 '1 = adCmdText
myCmd.CommandText = strQuery 'this is my query with ? placeholders
myCmd.Prepared = false 'i won't repeat it
Set myRs   = Server.CreateObject("ADODB.Recordset")
myRs.CursorType = 1 '0 = adOpenForwardOnly, 1 = adOpenKeyset, ...
myRs.LockType = 3 '3 = adLockOptimistic, ...

(here i prepare query and parameters)

myRs.Open myCmd

this should be correct, it is always the same for a lot of queries

1st case:

strQuery = SELECT * FROM TableA WHERE TableA.ID IN (SELECT myIDs FROM TableB WHERE TableB.NumericField=?) AND StringField=?

then i use this command cycling on two arrays, one for parameter values, another for types:

myCmd.Parameters.Append myCmd.CreateParameter("@Par" & i, VTypes(i), 1, 0, VValues(i))

here VValues is [8975, "011M1005D"] and VTypes is [3, 8]

result is 12 records, it is correct, i see those records on my database

2nd case (i only changed order of my two AND clauses):

strQuery = SELECT * FROM TableA WHERE StringField=? AND TableA.ID IN (SELECT myIDs FROM TableB WHERE TableB.NumericField=?)

VValues is ["011M1005D", 8975] and VTypes is [8, 3]

result is ZERO records, not an error but simply no records WHY in your opinion ??? i would expect the same 12 records as before, the AND logic operator is simmetric!

3rd case (i use values on 2nd case instead of parameters):

strQuery = SELECT * FROM TableA WHERE StringField='011M1005D' AND TableA.ID IN (SELECT myIDs FROM TableB WHERE TableB.NumericField=8975)

result is correct: 12 records

curious, with plain values the order seems not important anymore

i also tried to debug the parameters collection, printing for each parameter it's value and it's type in order to check them just a line before the Open commmand: they seems correct and in the correct order in both cases

you are last chance or i have to rearrange a lot of code using some JOIN (but i really would understand my mistake before!). thank you for patience

Arturo
  • 1
  • 1
  • The declaration of parameters in the `Parameters` collection when using `CommandType` `adCmdText` is entirely based on the order they are declared. The ordinal position is everything, so if you change the order of the `?` in your SQL statement you need to change the order of the appended parameters as well. – user692942 Aug 10 '22 at 16:06
  • hallo and thank you what you say is correct. as you see in my 1st and 2nd cases i changed the order of the ? in strQuery and the order of two parameters in VValues and VType. i checked: they seems correct in values and order – Arturo Aug 10 '22 at 16:23
  • I would use [SQL Profiler](https://learn.microsoft.com/en-us/sql/tools/sql-server-profiler/sql-server-profiler?view=sql-server-ver16) to see if the query you are expecting is what is executed (assuming you're using SQL Server). – user692942 Aug 10 '22 at 16:25
  • going to see what it is... sorry. anyway my db is Access this time – Arturo Aug 10 '22 at 16:26
  • anyway i already tested my queries directly in Access editor but i cannot really check if there is a mistake in passing parameters, i'm only able to test the 3rd case above but not the 2nd, easy but not so useful – Arturo Aug 10 '22 at 16:36
  • You can loop through the `Parameters` collection to determine the order of the passed parameters using a `For Each` loop, just in case the loop that builds them is doing something weird. – user692942 Aug 10 '22 at 16:39
  • Try to use parenthesis in sql query: `SELECT * FROM TableA WHERE (StringField=?) AND (TableA.ID IN (SELECT myIDs FROM TableB WHERE TableB.NumericField=? and myIDs is not null))`. And also take care for skipping `null`-s in `IN` subquery – Artem Popov Aug 11 '22 at 12:56
  • thank you user692942. that's the way i tried, really i didn't notice anything weird. only doubt: the string parameter dowsn't pass really as string (because i cannot response.write any delimiter '.....', i can just check the type of the value and it is correct at least). thank you Artem. i did not try the parenthesis on the first condition. going to try – Arturo Aug 11 '22 at 15:24
  • solution of Artem are not fine, sorry. i tried parenthesis and that "IS NOT NULL". no way. but i did another test changing the order of parameters: cmd: SELECT * FROM TableA WHERE StringField=? AND TableA.ID IN (SELECT myIDs FROM TableB WHERE TableB.NumericField=?) lista parametri [name, type, value]: [@Par0, 3, 8975] [@Par1, 8, 011-PJM1-005D] recordcount: 12 – Arturo Aug 11 '22 at 15:48
  • That is finally right. It seems that the first parameter i set go to the ? placeholder inside the subquery, despite the order they have in the command string. Before goes the parameter inside the subquery (because it is evaluated first, maybe?) then the parameter who comes first in the string but is evaluated as the second one. Some expert of you can confirm the logic? Here it runs fine but this way is very hard to mantain some kind of code readability if the query and the set of parameters become just a little more complex – Arturo Aug 11 '22 at 16:00

0 Answers0