Querydef.Parameters values disappear after setting Querydef.SQL property with PARAMETERS clause

Question

I have a table into which I'm importing data from an external source, updating the existing values if the record is present, and inserting a new row if it's not. For that purpose I'm using a parameterized query, and of course I'd like to reuse the parameter definitions and their values for both cases.

What I'm seeing is that if I set the QueryDef's .SQL property and then define the parameter values, it works fine. But if I do it the other way around (as I'd like to do in the portion of the function that does the insert), the values I've set for the parameters disappear, so that I have to set them again for the insert to work; if I don't, I get the error "Too few parameters. Expected 2."

Here's the relevant bit of code:

Set qdef = db.CreateQueryDef("")

strSQLParams = "PARAMETERS [P1] Text(25), [P2] Text(25); "
strSQLBody = "UPDATE T1 SET [Field1] = [P1] WHERE [Field2] = [P2]; "

qdef.SQL = strSQLParams & strSQLBody
qdef.Parameters("[P1]").Value = element(key1)
qdef.Parameters("[P2]").Value = element(key2)
qdef.Execute

If qdef.RecordsAffected = 0 Then
    strSQLBody = "INSERT INTO T1 ([Field1], [Field2]) VALUES ([P1], [P2]); "
    qdef.SQL = strSQLParams & strSQLBody

    '!!! if I don't set these again, I get an error "Too few parameters"
    qdef.Parameters("[P1]").Value = element(key1)
    qdef.Parameters("[P2]").Value = element(key2)
    qdef.Execute

Else
End If
Set qdef = Nothing

What's the cause of this? And is there any other workaround other than, as above, setting the parameter values again?

Gustav · Accepted Answer · 2019-05-24 07:46:52Z

1

After resetting the SQL, it becomes two different queries. Thus, the parameters must be assigned again.

answered May 24, 2019 at 7:46

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Querydef.Parameters values disappear after setting Querydef.SQL property with PARAMETERS clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related