Reusing recordset variable parameters

[Guest]

Sorry for the double post guys but I've had no luck in the general forum. Please help me find an answer to this.

--------------------------------------

I've been trying to reuse recordset variable parameters but when I try it get:

<variable>

"is an invalid variable name, it does not appear in the SQL"

Here is the SQL:

SELECT [Client Record ID], [Client Initials], Age, Sex, [Surgery AKA], [Referrer name], Comments, [Date referral made], [Counsellor First Name], [Counsellor Surname]
FROM dbo.[SYS New Referral Registration Record]
WHERE ([Client Record ID] = varClient AND [Counsellor Code] = varCounsellor) OR ([Client Record ID] = varClient AND [Owner] = varCounsellor)

.....................................

The variables are specified correctly but it won't use it more than once. When I refer to the varible once it works fine.

Any ideas???

[Newsgroup_User]

Ding_Dong wrote:

> WHERE ([Client Record ID] = varClient AND [Counsellor Code] =
> varCounsellor) OR ([Client Record ID] = varClient AND [Owner] =
> varCounsellor)

If it is mssql you need more brackets as below

> WHERE (([Client Record ID] = varClient) AND ([Counsellor Code] =
> varCounsellor)) OR (([Client Record ID] = varClient) AND ([Owner] =
> varCounsellor))

--

[Newsgroup_User]

There's actually a bug in DW's new recordset code. When you re-use a
parameter, it only attaches the parameter one time. If you were to look in
code view, you'd see something like:

myCmd.Source = "SELECT ... WHERE this = ? AND that = ? AND (here = ? OR
there = ?)"

This means you'll need to see 4 parameters attached, one for each question
mark. To re-use a variable, copy and paste the line that attaches the
variable into the appropriate position (the first parameter attached goes
with the first question mark and so on). You'll have to change the name
slightly (for instance, from "@varClient" to "@varClient1") to avoid errors
that say the parameter already exists in the collection.


"Ding_Dong" <webforumsuser@macromedia.com> wrote in message
news:es19s7$e6l$1@forums.macromedia.com...
> Sorry for the double post guys but I've had no luck in the general forum.
> Please help me find an answer to this.
>
> --------------------------------------
>
> I've been trying to reuse recordset variable parameters but when I try it
> get:
>
> <variable>
>
> "is an invalid variable name, it does not appear in the SQL"
>
> Here is the SQL:
>
> SELECT [Client Record ID], [Client Initials], Age, Sex, [Surgery AKA],
> [Referrer name], Comments, [Date referral made], [Counsellor First Name],
> [Counsellor Surname]
> FROM dbo.[SYS New Referral Registration Record]
> WHERE ([Client Record ID] = varClient AND [Counsellor Code] =
> varCounsellor)
> OR ([Client Record ID] = varClient AND [Owner] = varCounsellor)
>
> .....................................
>
> The variables are specified correctly but it won't use it more than once.
> When I refer to the varible once it works fine.
>
> Any ideas???
>
>

[Guest]

Thanks for the reply but I still get the invalid column names error when using the same variable twice. If I did this in Dreamweaver MX 2004 I know it works.

I have triple checked the columns and their names.

Anything else - keep them coming.