Solved

ASP SQL Statement

Posted on 1998-08-10
8
132 Views
Last Modified: 2013-12-25
Is is possible to write a dynamic SQL Statement?
The original statement is simple like == SELECT * FROM Inventory WHERE ID=4 ==.  I tried to change it to == SELECT * FROM Inventory WHERE ID=<% =Number %> == where Number is a local variable that changes according to action.  Please let me know how to make this possible.  Greatly appreciated.
0
Comment
Question by:dangel
  • 3
  • 3
  • 2
8 Comments
 
LVL 4

Accepted Solution

by:
mitek earned 100 total points
ID: 1858930
Yep, of course it's possible.

SQL = "SELECT * FROM Inventory WHERE ID = " & Number

Connection.Execute(SQL)

0
 
LVL 28

Expert Comment

by:sybe
ID: 1858931
Mitek, you posted yopur answer while I was typing it :)
It is abou the same


Yes it is possible

<%
Number = 4

' or if you get the number from a form that has been send

Number = Request.Querystring("number")

strSQL = "SELECT * FROM Inventory WHERE ID = " & Number
Response.write strSQL

If you want to use it to get a recordset

Set RS = Conn.Execute(strSQL)

%>
0
 
LVL 4

Expert Comment

by:mitek
ID: 1858932
SQL is a local variable here.
besides, i wouldn't use Number as a variable name. very confusing and may be conflicting with VB datatypes.

try to use something like snum or the like
0
 
LVL 4

Expert Comment

by:mitek
ID: 1858933
Sorry :)

this once happened to me, too :)))

very funny (wasn't funny for me though, when it happened :)

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 1

Author Comment

by:dangel
ID: 1858934
    Thanks for the help guys.  One more question I need to ask.
     My ASP pages are based on an Access97 database.  I get this error "Database Error: [Microsoft][ODBC Microsoft Access 97 Driver] Data type mismatch in criteria expression." when I try to retrieve data through == Select * from Inventory where ID='%%ID%%' == when ID is set to either AutoNumber or Number.  But I can't change it to Text because it won't work for other pages based on this database.
     What should I do to make it work?  
0
 
LVL 1

Author Comment

by:dangel
ID: 1858935
By the way, '%%ID%%' is submitted from a previous page in a hidden field == <input type="hidden" name="ID" value="<% =IDNUM %>"> where <% =IDNUM %> is a local variable.
0
 
LVL 28

Expert Comment

by:sybe
ID: 1858936
strSQL = "Select * from Inventory where ID=" & Request("ID")
Set RS = Conn.Execute(strSQL)
0
 
LVL 1

Author Comment

by:dangel
ID: 1858937
Thanks for all the help guys.  
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Accessibility and Usability are two concepts that seem to be closely related.  But, too many people seem to have a distorted perception of them. During last five years, those two words have come to the day-to-day work of almost every web develope…
Learn by example how to specify CSS selectors for Selenium WebDriver test automation software.
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now