Solved

ASP SQL Statement

Posted on 1998-08-10
8
137 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
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
 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our systems…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

739 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