Re: Variable in SQL statement

• Previous message: Val Mazur: "Re: Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source."
• In reply to: Don Starkey: "Variable in SQL statement"
• Messages sorted by: [ date ] [ thread ]

Date: Thu, 26 Feb 2004 22:40:12 -0500

Hi Don,

No, variable cannot be used inside of the SQL statement directly, but there
are several ways to do this. Best and fastest way to do this is to use
parameters (not concatenation). Concatenation is a short, but VERY dangerous
way to build your SQL statements, since it leads to injection into you
statement. You could would look a little bit bigger, but it worth it

Dim loCommand As ADODB.Command
Dim loParameter as ADODB.Parameter

Set loCommand = New ADODB.Command
Set loCommand.ActiveConnection = MyADOConnection

lcSQL="deSVLG.rscmdIGArpt.Open "SELECT Address, City, FirstName, IGADate,
IGAFee, LastName, MemberNo, StateZip, Remarks FROM SvlgMain WHERE
MemberNo >= ?"

loCommand.CommandText=lcSQL
loCommand.CommandType=adCmdText
Set loParameter = loCommand.CreateParameter("@MemberNo" , adInteger,
adParamInput)
loParameter.Value=MemNo
loCommand.Parameters.Append loParameter
Set MyADORecordset=loCommand.Execute

-- 
Val Mazur
Microsoft MVP
"Don Starkey" <imdonnie@msn.com> wrote in message 
news:eUoywvK$DHA.808@TK2MSFTNGP12.phx.gbl...
>I have a SQL statement:
>
> deSVLG.rscmdIGArpt.Open "SELECT Address, City, FirstName,  IGADate, 
> IGAFee,
> LastName, MemberNo, StateZip,  Remarks  FROM SvlgMain WHERE MemberNo >=  (
> MemNo)
>
> Where (MemNo) at the end is a variable integer
>
> I can operate the program OK by using MemNo as a Parameter in the data
> designer, but would prefer to use the variable in the text recordset
> opening.
>
> Can a variable be used in a SQL statement, and if so, how?
>
> Thanks
>
> 

• Previous message: Val Mazur: "Re: Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source."
• In reply to: Don Starkey: "Variable in SQL statement"
• Messages sorted by: [ date ] [ thread ]