using a variable in a SQL statement.

[Dean Earley]

> I was wondering if anyone knows if you can embed a variable into a SQL statement.ie:
>
> Dim dept as Integer
> dept = 11
> SELECT Name, Surname FROM Department WHERE deptno = '11';
>
> Replacing the number 11 in the sql statement with the variable name:
>
> SELECT Name, Surname FROM Department WHERE deptno = dept;?
>
> I want to be able to store the varible globally and use it to change the
> WHERE clause in the SQL statement as and when the variable is changed.

dept = 11
SQL="SELECT Name, Surname FROM Department WHERE deptno = '" & CStr(dept) & "';"

HTH

--
Dean Earley (dean.earley@icode.co.uk)
Assistant Developer

iCode Systems

[Des]

I was wondering if anyone knows if you can embed a variable into a SQL statement.ie:

Dim dept as Integer
dept = 11
SELECT Name, Surname FROM Department WHERE deptno = '11';

Replacing the number 11 in the sql statement with the variable name:

SELECT Name, Surname FROM Department WHERE deptno = dept;?

I want to be able to store the varible globally and use it to change the
WHERE clause in the SQL statement as and when the variable is changed.

[Craig Brown]

Des,

If your Select statement is simply imbedded in your VB code, yes, you can
do that.

It would be something like:

Dim sSQL As String
Dim sVar as String
Dim sQuote as String

sQuote = Chr(39)
sVar = "Whatever you want it to be now..."
sSQL = "SELECT x, y, z From tblWhatever Where fldA = "
& sQuote & sVar & sQuote

If you are using a DBMS that allows stored procedures, you may want to look
into putting your SQL statement there instead.

Craig Brown




"Des" <desmond.kavanagh@student.shu.ac.uk> wrote:
>
>I was wondering if anyone knows if you can embed a variable into a SQL statement.ie:
>
>Dim dept as Integer
>dept = 11
>SELECT Name, Surname FROM Department WHERE deptno = '11';
>
>Replacing the number 11 in the sql statement with the variable name:
>
>SELECT Name, Surname FROM Department WHERE deptno = dept;?
>
>I want to be able to store the varible globally and use it to change the
>WHERE clause in the SQL statement as and when the variable is changed.

[Des]

Thanks, that's been really helpful. I didn't think i'd get a reply so soon.
You both saved me a lot of blood, sweat and tears!

Des