| Author |
 Topic Search  Topic Options
|
reverett 
Newbie
Joined: 19 March 2005
Status: Offline
Points: 9
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Using variables in SELECT FROM WHERE
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!
|
 |
bootcom
Senior Member
Joined: 26 February 2005
Location: United Kingdom
Status: Offline
Points: 259
|
Post Options
Thanks(0)
Quote Reply
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 &"';"
|
|
reverett
Newbie
Joined: 19 March 2005
Status: Offline
Points: 9
|
Post Options
Thanks(0)
Quote Reply
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!
|
|
dj air
Senior Member
Joined: 05 April 2002
Location: United Kingdom
Status: Offline
Points: 3627
|
Post Options
Thanks(0)
Quote Reply
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 &";"
|
|
reverett
Newbie
Joined: 19 March 2005
Status: Offline
Points: 9
|
Post Options
Thanks(0)
Quote Reply
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!
|
|
reverett
Newbie
Joined: 19 March 2005
Status: Offline
Points: 9
|
Post Options
Thanks(0)
Quote Reply
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!
|
|
dj air
Senior Member
Joined: 05 April 2002
Location: United Kingdom
Status: Offline
Points: 3627
|
Post Options
Thanks(0)
Quote Reply
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.
|
|
reverett
Newbie
Joined: 19 March 2005
Status: Offline
Points: 9
|
Post Options
Thanks(0)
Quote Reply
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><FONT COLOR='#000066' FACE='arial' SIZE='2'>" & strField1 & "</FONT></TD>"
Response.Write "<TD><FONT COLOR='#000066' FACE='arial' SIZE='2'>" & strField2 & "</FONT></TD>"
Response.Write "<TD><FONT COLOR='#000066' FACE='arial' SIZE='2'>" & strField3 & "</FONT></TD>"
Response.Write "<TD>" & "<FONT COLOR='#000066' FACE='arial' SIZE='2'>" & rsObject("dept") & "</FONT>" & "</TD>"
Response.Write "<TD>" & "<FONT COLOR='#000066' FACE='arial' SIZE='2'>" & rsObject("loc") & "</FONT>" & "</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.
|
|
