Classic ASP Discussion - Create Variable to add into SQL Statement?

Print Page | Close Window

Create Variable to add into SQL Statement?

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=28226
Printed Date: 28 March 2026 at 1:39pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Create Variable to add into SQL Statement?

Posted By: kennywhite
Subject: Create Variable to add into SQL Statement?
Date Posted: 11 January 2010 at 9:02pm

Our IT department has a blog where we record all of our daily tasks. One of the fields that we enter information into is for the serial number of the device that we serviced.

I want to add a function to click on the serial number and be taken to a page that will show you every entry containing this serial number.

What is the easiest way to do this?

I'm thinking the SQL statement of the page displaying the entries would contain

WHERE serial_number = " & VARIABLE & "

But how would I create that variable on the main page, where we would click on the serial number?

Is there a simpler way to do this?

Thanks for reading!

Replies:

Posted By: godeep
Date Posted: 31 January 2010 at 4:53am

Really quick run down. hope it helps. Hopefully someone can help me with my problem.

Dim MyVariable

MyVariable = 123456789123456789 <---- This is your serial number if all numbers.

MyVariable = "1AddSE2sDDFVDSDFEK" <---- This is your serial number if numbers and letters.

<a href="resultspage.asp?serialNum=<%=MyVariable%>">go page to display results</a>

----

results.asp

if its only numbers

SELECT * FROM records WHERE serial_number = '"&request.querystring("serialNum")&"'

if it includes letters

SELECT * FROM records WHERE serial_number LIKE '"&request.querystring("serialNum")&"'

Posted By: kennywhite
Date Posted: 04 February 2010 at 7:03pm

This works great. Thanks for the help.

These serial numbers are stored in an Access database. The serial numbers are stored in a certain column and the details of the task are stored in another. Is there a way to check both the serial_number column and the details column for a match with the serial number that you clicked?

For instance, say that someone had entered one serial number into the serial number field, but performed the same task on 2 different computers and noted the second serial number in the details field.

Can I look for a match in both of those fields?

I have the link on the main page like so,

On the results page I have this:

SELECT * FROM blog WHERE serial_number LIKE '"&request.querystring("serialNum")&"'

Is there a way to display the results from both columns?

Thanks again!

Posted By: Scotty32
Date Posted: 06 February 2010 at 10:41am

If you want to access a specific row, then you should really be using ID numbers.

If you havent already, create a field in the table called "Row_ID" and make it an autonumber (i havent used access in years so dont remember exactly what its called)

You can then pass the Row_ID number around to specify specific records.

Also I would recommend against the "like" statement, as this is ment to allow you do close matchs eg

if you have "where field like 'A%'" then it will find all rows that the field starts with the letter A
the % is a wild card, so you could do " '%A%' " to find all containing A or " '%A' " all ending in A

what you should be doing is:

SELECT * FROM blog WHERE serial_number = '"&request.querystring("serialNum")&"'

And I would recommend taking the direct request out like so:


Dim strSerialNumber
strSerialNumber = request.querystring("serialNum")
SELECT * FROM blog WHERE serial_number = '"& strSerialNumber &"'

If you use the Row_ID then I would do the following, which will help protect against SQL Injection Attacks:


Dim intSerialID

intSerialID = Request.Querystring("SID")
if isNumber(intSerialID) then inSerialID = clng(intSerialID) else intSerialID = 0

SELECT * FROM blog WHERE Row_ID = "& intSerialID &"

This will make sure that intSerialID will always be a number

-------------
S2H.co.uk - http://www.s2h.co.uk/wwf/" rel="nofollow - WebWiz Mods and Skins

For support on my mods + skins, please use http://www.s2h.co.uk/forum/" rel="nofollow - my forum .

Posted By: kennywhite
Date Posted: 08 February 2010 at 4:59pm

If you use the Row_ID then I would do the following, which will help protect against SQL Injection Attacks:


Dim intSerialID

intSerialID = Request.Querystring("SID")
if isNumber(intSerialID) then inSerialID = clng(intSerialID) else intSerialID = 0

SELECT * FROM blog WHERE Row_ID = "& intSerialID &"

I'm not quite sure that I understand this. Will this search for the serial number in the entire row?

Posted By: Scotty32
Date Posted: 10 February 2010 at 7:20pm

I am afraid I do not know how your database is structured so I do not know the answer.

If your database is structured like this (which I recommended above)

Row_ID | Customer   | Serial Number
----------|---------------|------------------------
  1         | John          | WER-TYU-IOP
  2         | Mr Smith   | ASD-FGH-JKL

Then you will be able to get the Serial Number by using the Row_ID, rather than the Serial Number directly.
As I said, this would make your SQL statements far more secure from hacking attempts.

-------------
S2H.co.uk - http://www.s2h.co.uk/wwf/" rel="nofollow - WebWiz Mods and Skins

For support on my mods + skins, please use http://www.s2h.co.uk/forum/" rel="nofollow - my forum .

Posted By: kennywhite
Date Posted: 10 February 2010 at 9:42pm

hmmm... I tried to do as your example showed, but I didn't get the expected results.

That is how my database is laid out. Instead of "Row_ID" the column is just called "id".

Here is how my code looks now.

The main page is still the same

<a href="results.asp?serialNum=<%=rs("serial_number")%>"><%=rs("serial_number")%></a>

It pulls the serial number from the database.

The results page has been changed to:

Dim intSerialID

intSerialID = Request.Querystring("SID")
if isNumber(intSerialID) then inSerialID = clng(intSerialID) else intSerialID = 0

SELECT * FROM blog WHERE id = "& intSerialID &"

All of the serial numbers are mixed characters. Does that make a difference?

I am only getting results that have a match in the serial_number field in the database.

I entered a test serial number into both the serial_number field and the details field, but the one with the serial number in the details field does not show up in the results.

Posted By: Scotty32
Date Posted: 11 February 2010 at 8:08pm

If your table has "id" then use that where I have used "Row_ID"

How is your database structured, is the "main page" you mention running off the same Serial Number table as the results page?

If so you will need to change the link to:

<a href="results.asp?SID=<%=rs("id")%>"><%=rs("serial_number")%></a>

If not, you will either need to restructure your database (store the "id" from the serial number table in which ever table is displayed on the "main page"

If that is not an option then you will need to go with the original answer, Let me know and i will post the best way to do this.

Ideally you would want to pass around unique ID numbers for each row rather than the serial number its self (or customers name if thats what your doing)

But as I said, if you could tell me your database structure. If this is sensitive information, feel free to send me an email (which is in my profile)

-------------
S2H.co.uk - http://www.s2h.co.uk/wwf/" rel="nofollow - WebWiz Mods and Skins

For support on my mods + skins, please use http://www.s2h.co.uk/forum/" rel="nofollow - my forum .

Posted By: kennywhite
Date Posted: 15 February 2010 at 4:18pm

I tried changing the link like you said, but now it seems to bring up random results. Sometimes, depending on the serial number, it doesn't even bring up the serial number that I clicked.

Here is a picture of my database.

http://www.whitehairproductions.com/stuff/db.JPG" rel="nofollow - http://www.whitehairproductions.com/stuff/db.JPG

I hope this identifies why I'm not getting the expected results.

Thanks for your help.

Posted By: kennywhite
Date Posted: 17 February 2010 at 6:39pm

For now I am using the original solution. It works well enough, but I would really love to find a way to check the whole database for a match.

Another question: How can I display the number of results found in the database?

Thanks!

Posted By: dpyers
Date Posted: 21 February 2010 at 5:58pm

Your DB seems to use a mix of upper and lower case serial numbers.
VBA, the language used by the MS Access program itself, is not case sensitive.
ASP however is case sensitive. When using ASP to compare two fields pulled from an Access DB, you need to force the comparison to either compare both fields as upper or lower case using either UCase or LCase.

Field1 = "a"
Field2 = "A"

"If Field1 = Field2"                         will compare as FALSE
"If UCase(Field1) = UCase(Field2)"   will compare as TRUE
"If LCase(Field1) = LCase(Field2)"    will compare as TRUE

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

Lead me not into temptation... I know the short cut, follow me.

Posted By: kennywhite
Date Posted: 07 April 2010 at 6:23pm

Is there a way that I can, after going to the results page, click on another link to filter the information displayed even further? For instance, I'm looking at every entry containing this serial number, then I want to click on whatever category I desire.

I am using this same code for another website when we list all of our assets. On the main page I have a menu where the user can select which site to view. If they click "Ontario",

<a href="location.asp?LocDisplay=Ontario">Ontario</a>

They are then taken to a page that displays only the assets located in Ontario.

SELECT * FROM assets WHERE Location LIKE '"&request.querystring("LocDisplay")&"'

On this page, there is another menu where they can filter the results further by selecting a specific type of device, such as Desktop, Laptop, Server, Monitor, etc.

I would like to make the links on this menu take the user to another page (or stay on this one if possible) where they will view just the Desktops (or whatever they click) from that specific location.

I want to pull the LocDisplay variable and insert it into the link, followed by a variable for the type of device clicked.

Right now, I am testing this out by entering the link as follows:

<a href="location_device.asp?LocDisplay=Ontario?DeviceDisplay=Desktop">Desktops</a>

This will open the location_device.asp page, but no data is displayed.

The SQL statement is like so:

SELECT * FROM assets WHERE Location LIKE '"&request.querystring("LocDisplay")&"' AND DeviceType LIKE 
'"&request.querystring("DeviceDisplay")&"'

What am I doing wrong? I'm sure it has to be something I did in the link.

Thanks!