Classic ASP Discussion - Using variables in SELECT FROM WHERE

Print Page | Close Window

Using variables in SELECT FROM WHERE

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=14411
Printed Date: 30 March 2026 at 11:30am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Using variables in SELECT FROM WHERE

Posted By: reverett
Subject: Using variables in SELECT FROM WHERE
Date Posted: 24 March 2005 at 2:59pm

Can anyone help me with syntax on the strSQL = line? I've tried putting the variable between ' ' and " ".

Dim strWherel
Dim strSearchl

Set strWhereval = "a"
Set strSearchva = "hello"

strSQL = "SELECT a, b, c, FROM x WHERE strWhere LIKE strSearch;"

Thanks!

Replies:

Posted By: bootcom
Date Posted: 24 March 2005 at 3:29pm

reverett wrote:

Can anyone help me with syntax on the strSQL = line? I've tried putting the variable between ' ' and " ".

Dim strWherel
Dim strSearchl

Set strWhereval = "a"
Set strSearchva = "hello"

strSQL = "SELECT a, b, c, FROM x WHERE strWhere LIKE strSearch;"

Thanks!

Aye, try this, dont forget that the SQL line is like a response write line, so if your throwing ASP variables in there dont forget to close of the line and include the ASP stuff

. If your doing it with numbers/boolean values then knock off the '' s .... like this "& strWhere &"

strSQL = "SELECT a, b, c, FROM x WHERE '"& strWhere &"' LIKE '"& strSearch &"';"

Posted By: reverett
Date Posted: 25 March 2005 at 1:43am

Thanks for the feedback. I tried your example. Here is the exact code I'm working on and the result when displayed:

strSQL = "SELECT ext.ext, ext.first, ext.last, ext.dept, ext.loc FROM ext WHERE '"& strWhere &"' LIKE '"& strLike &"' ORDER BY '"& strOrder &"';"
Response.Write strSQL

SELECT ext.ext, ext.first, ext.last, ext.dept, ext.loc FROM ext WHERE 'ext.last' LIKE 'e%' ORDER BY 'ext.last';

The values being in ' ' cause strange things to happen. Do you know what I need to change to have Response.Write display:

SELECT ext.ext, ext.first, ext.last, ext.dept, ext.loc FROM ext WHERE ext.last LIKE 'e%' ORDER BY ext.last;

Thanks again!

Posted By: dj air
Date Posted: 25 March 2005 at 9:15am

try
strSQL = "SELECT ext.ext, ext.first, ext.last, ext.dept, ext.loc FROM ext WHERE "& strWhere &" LIKE '%"& strLike &"%' ORDER BY "& strOrder &";"

Posted By: reverett
Date Posted: 25 March 2005 at 10:17am

I made the changes you suggested. It didn't like the added % in LIKE '%"& strLike &"%' but the other two changes did the trick:

strSQL = "SELECT ext.ext, ext.first, ext.last, ext.dept, ext.loc FROM ext WHERE "& strWhere &" LIKE '"& strLike &"' ORDER BY "& strOrder &";"

Response.Write strSQL

Results in:

SELECT ext.ext, ext.first, ext.last, ext.dept, ext.loc FROM ext WHERE ext.first LIKE 'e%' ORDER BY ext.first;

Thank you very much dj!

Posted By: reverett
Date Posted: 25 March 2005 at 12:24pm

Another question related to the same project? Can you help me with syntax on:

variable = rsObject("fieldname")

Catch is, I don't want to set the variable to the value of rsObject("fieldname"), I'm trying to set it to hold the actual text rsObject("fieldname")

If I were to:

Response.Write variable

The display would be:

rsObject("fieldname")

I've a few different things with no luck.

Thanks!

Posted By: dj air
Date Posted: 25 March 2005 at 1:27pm

is this a set field name or dependant on something.

for example

first time it maybe last, second time it may be first etc.

have this is an example of your idea.

i have 2 fields called called First and Last

on the first time you want to get rsObject("First") and the 2nd you want rsObject("last")

to be show as the above.

to be suire i need to now how you are using this info.. for example if a querystringvalue = "1" its using the first

else if using "2" its using Last

etc.

Posted By: reverett
Date Posted: 25 March 2005 at 2:07pm

Thanks dj. I was able to solve the first issue by using:

    'Set Position in Table
    If strSearchby = "first" Then
      strDesc1 = "First"
      strDesc2 = "Last"
      strDesc3 = "Ext"
      strField1 = "rsObject('first')"
      strField2 = "rsObject('last')"
      strField3 = "rsObject('ext')"
        ElseIf strSearchby = "last" Then
          strDesc1 = "Last"
          strDesc2 = "First"
          strDesc3 = "Ext"
          strField1 = "rsObject('last')"
          strField2 = "rsObject('first')"
          strField3 = "rsObject('ext')"
        ElseIf strSearchby = "ext" Then
          strDesc1 = "Ext"
          strDesc2 = "First"
          strDesc3 = "Last"
          strField1 = "rsObject('ext')"
          strField2 = "rsObject('first')"
          strField3 = "rsObject('last')"
    End If

But...

When I use the following to display the table:

'Display recordsets in table
 Response.Write "<TR>"
 Response.Write "<TD>" & strField1 & "</TD>"
 Response.Write "<TD>" & strField2 & "</TD>"
 Response.Write "<TD>" & strField3 & "</TD>"
 Response.Write "<TD>" & "" & rsObject("dept") & "" & "</TD>"
 Response.Write "<TD>" & "" & rsObject("loc") & "" & "</TD>"
 Response.Write "</TR>"

The result is:

Last	First	Ext	Department	Location
rsObject('last')	rsObject('first')	rsObject('ext')	Dept	Location
rsObject('last')	rsObject('first')	rsObject('ext')	Dept	Location

I need to change the table code so it displays the actual data. I beleive it to be another syntax issue.

Posted By: dj air
Date Posted: 25 March 2005 at 2:39pm

the onlyt way i can see is to set another vieriable for each one you want to show within the above if /else if statement.

like strdisplayCell1 and strDisplayCell2 etc.. then add within the table row to response.write strDisplayCell1 under the last collumn and StrDisplayCell2 under the first collumn..

and within the if statement you set the value of the field.

Posted By: reverett
Date Posted: 25 March 2005 at 3:04pm

Sorry, you lost me on that one. Is there no way to midify the sytax to display the value (val(variable) or something) versus the name of the vaiable?

Posted By: dj air
Date Posted: 25 March 2005 at 3:10pm

sorry about that

the best way i think of doing it is

when you set the name of the veriable veriablename = "rsObject("First")"

set another veriable holding rsObject("First") 's value

then when you response.write the row use the new veriable which will write the object/fields value

Posted By: reverett
Date Posted: 25 March 2005 at 3:54pm

Sorry, I guess I'm a little slow. If I'm trying to use the variable to both display the value and set the column position in the table how would I use the second variable and keep it dynamic?

strSearchby is determined by a radio button selection. It also sets which values are in columns 1, 2 and 3.

    If strSearchby = "first" Then
      strDesc1 = "First"
      strDesc2 = "Last"
      strDesc3 = "Ext"
      strField1 = "rsObject('first')"
      strField2 = "rsObject('last')"
      strField3 = "rsObject('ext')"
        ElseIf strSearchby = "last" Then
          strDesc1 = "Last"
          strDesc2 = "First"
          strDesc3 = "Ext"
          strField1 = "rsObject('last')"
          strField2 = "rsObject('first')"
          strField3 = "rsObject('ext')"
        ElseIf strSearchby = "ext" Then
          strDesc1 = "Ext"
          strDesc2 = "First"
          strDesc3 = "Last"
          strField1 = "rsObject('ext')"
          strField2 = "rsObject('first')"
          strField3 = "rsObject('last')"
    End If

If I follow you correctly, you are saying to set a second variable to hold the value of rsObject("x") but still need to plug that back into the table. At that point it's no longer changing based on the radio button selection and I could use rsObject("x") as in the last two columns.

Am I missunderstanding you?

Posted By: dj air
Date Posted: 25 March 2005 at 4:10pm

yok reading what you are doing .., within the loop of each record you need to get the veriable for the last 2 cells

DEPT and LOC.

the only way really is set another if statement within the loop/next for each record and check what the last 2 cells should contain

so have another If statement like the above but set a temp veriable to the correct fields from the database

then response .write that veriable within the cells. and re do that for every records

f I follow you correctly, you are saying to set a second variable to hold the value of rsObject("x") but still need to plug that back into the table. At that point it's no longer changing based on the radio button selection and I could use rsObject("x") as in the last two columns.

that is what im referring to.. do an if statement on this within the loop/next sequence for each records. its the only way i can think really of doing so

Posted By: reverett
Date Posted: 25 March 2005 at 5:14pm

I really appreciate your help dj. I'll probably rethink my overall approach. It seemed like a good idea at the time. One thing I'm still perplexed with? Why I can use rsObject("x") in the table:

Response.Write "<TD>" & "" & rsObject("dept") & "" & "</TD>"

and have it plug in the appropriate value but I can set something up that with use the -value- of strFieldx (whick would be rsObject("x")) and have it work the same way and be supported by the same loop.

I'm pretty new at this and I'm wondering if I'm getting the capabilities/sytax of SQL and VBScript confused.

Can you explain why I can't do this? I can see the logic I'm after but I'm still too green to know why I can't do it. In this case I beleive it would help me to understand why I can't.

Either way, thank you very much dj.

Posted By: dj air
Date Posted: 26 March 2005 at 10:14am

hi again, no problem with helping.

im not 100% sure what you said above but.

to show the value of a field within a database you use response.write (rsObject("Fieldname"))

or <%= rsObject("FieldName") %>

or you can set a veriable to hold the value

strValue = rsObject("FieldName")

a website that may help explain a few things is www.devguru.com

it shows all aspects of asp/VB and gives examples etc.

i hope that helps.

just incase.. what is the section for . i may be able to advise on a way