Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Pull data from SQL using ASP

Status
Not open for further replies.
perryair

perryair

IS-IT--Management
Apr 7, 2005
91
IL
Hi Guys...
I need your help since I am stuck here with the following code... what I need is to display data from SQL database
using ASP with '?' from another page.

The '?' query is:
Please help... Thanks.


<html>

<head>
<%

Set rstCurrent = Server.CreateObject("ADODB.Recordset")
rstCurrent.activeconnection= ("DRIVER={SQL Server};SERVER=SQL2000;DATABASE=database1;UID=user;PWD=password")
rstCurrent.source = "SELECT * FROM TodaySale"

rstCurrent.Open

ShowProduct = rstCurrent("SaleItem")
rstCurrent.movefirst
do while not rstCurrent.eof

Item="ShowProduct"
rstCurrent.Close

%>



<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>TodaySale</title>
</head>

<body>

<table border="1" width="100%" id="table1">
<tr>
<td>
<p align="center"><%=Item%></td>
</tr>
</table>

</body>

</html>
 
Sort by date Sort by votes
No...
I just could not use my code since I receive error message
and I can't understand why....
 
Upvote 0 Downvote
no... standard internet explorer error.
You mean my code should be okay ???? :)
 
Upvote 0 Downvote
Some points:

1. You don't use your querystring values anywhere in your code
2. You are selecting *everything* from the table 'TodaySale'
3. If this table is ever empty your code will fail as you try to access the recordset without checking if it has any content.
4. You never use the dynamically created variable 'ShowProduct', instead you use the constant 'Item'.

Not to mention several performance related issues, such as using 'SELECT * ' (you should explicitly name the fields you want), and you could use GetRows and should really use a stored procedure. But first things first..

So, the common approach to selecting 'specific' data from the database using a querystring (the bit after the'?') requires that you get the querystring value:

sItemID = replace(trim(request.querystring("ItemID")),"'","")

And then to use that variable in a WHERE clause in your SQL Statement:

sSQL = "SELECT SaleItem FROM TodaySales WHERE ItemID = '" & sItemID & "';"

I've assumed that ItemID is not numeric, therefore have wrapped '' chars around the value - if it's a number remove these.

Also, you should put a check in before you access the opened recordset:

if Not(rstCurrent.EOF and rstCurrent.BOF) then
...etc...

Do a search on google and this site for 'ASP querystring database' and you'll find numerous faq's/tutorials etc.

A smile is worth a thousand kind words. So smile, it's easy! :)
 
Upvote 0 Downvote
Looks like a couple of potential issues.

1. You appear to be passing ItemID as the parameter and then not using it. Maybe you should take the incoming ItemID into a local variable and then use it in your querystring:
Code: 
myID = cstr(Request.QueryString("ItemID"))
myQry = "SELECT * FROM TodaySale WHERE yourIDFieldname =" & myID
rstCurrent.source = myQry
rsCurrent.Open
Your recordset now contains all records with yourIDFieldname matching ItemID passed in. Now just loop through the recordset (at the moment you do a MoveFirst and don't do any looping). Instead of:
ShowProduct = rstCurrent("SaleItem")
rstCurrent.movefirst
do while not rstCurrent.eof

Item="ShowProduct"
Try in the <body> section:
Code: 
<%rsCurrent.MoveFirst%>
<table border="1" width="100%" id="table1">
<%Do While Not rsCurrent.EOF%>
<tr>
        <td>
        <p align="center"><%=rsCurrent("ProductName")%></td>
    </tr>
<%rsCurrent.MoveNext
Loop%>

Don't forget to Close your recordset object & connection object and set them to Nothing

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts
 
Upvote 0 Downvote
Now I see in the event log: "800a01a8|Object_required:_''"
I've changed the code as below:

=======================================================
<html>

<head>
<%

myID = cstr(Request.QueryString("ItemID"))
myQry = "SELECT * FROM sale WHERE ItemName =" & myID
Set rstCurrent = Server.CreateObject("ADODB.Recordset")
rstCurrent.activeconnection= ("DRIVER={SQL Server};SERVER=SQL2000;DATABASE=database1;UID=userid;PWD=password")
rstCurrent.source = myQry
rstCurrent.Open

%>



<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>TodaySale</title>
</head>

<body>

<%rstCurrent.MoveFirst%>
<table border="1" width="100%" id="table1">
<%Do While Not rstCurrent.EOF%>
<tr>
<td>
<p align="center"><%=rstCurrent("ItemName")%></td>
</tr>
<%rsCurrent.MoveNext
Loop%>

</table>

</body>

</html>
====================================================

Thanks a lot for your help!!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
write HTML code within ASP line
Replies
1
Views
765
xwb
xwb
leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
788
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
689
penguinspeaks
penguinspeaks
Swi
  • Locked
  • Question
JQUERY AJAX POST and how to receive data via Classic ASP
Replies
0
Views
843
Swi
Swi
how2trainyourdragon
  • Locked
  • Question
Request.Server variables blank for classic ASP
Replies
12
Views
1K
GriffMG
GriffMG

Part and Inventory Search

Sponsor

Back
Top