[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

publish result set from a Stored Procedure result from SQL SERVER into an ASP

Posted on 2007-03-26
7
Medium Priority
?
264 Views
Last Modified: 2012-06-27
I need 2 basic pages in ASP. I have the iis setup and server with the database. This is what I need
I need 2 ASP pages:
1. connection
2. Datapage with a print button.
 I want to able to call a page and view result set out of 2 stored procedures from a SQL Server DB. I am looking for a just read only data, I don't need edit functinality with it.  And have the ability to print the result set.

It could be just as simple as first name , last name test page.asp.

Thanks

0
Comment
Question by:Khanax
[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
  • 2
  • 2
7 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 18796031

Set conn = server.CreateObject("ADODB.Connection")
conn.Open "your database connection string here"
Set rs = server.CreateObject("ADODB.recordset")
sql = "SELECT col1, col2, col3 from table"
rs.Open sql,conn
If Not (rs.Eof and rs.bof) Then
 while not rs.eof
      Response.write rs("col1") & "<br>"
      Response.write rs("col2") & "<br>"
      Response.write rs("col3") & "<br>"
 wend
end if
0
 
LVL 35

Expert Comment

by:YZlat
ID: 18796053
for stored procedure try

Set conn = server.CreateObject("ADODB.Connection")
conn.Open "your database connection string here"
Set rs = server.CreateObject("ADODB.recordset")

rs=conn.execute "exec storedProcedureName"
If Not (rs.Eof and rs.bof) Then
 while not rs.eof
      Response.write rs("col1") & "<br>"
      Response.write rs("col2") & "<br>"
      Response.write rs("col3") & "<br>"
 wend
end if

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18796289
Just a slight change to YZLat's solution (no points please):

Set conn = server.CreateObject("ADODB.Connection")
conn.Open "your database connection string here"
'  Set rs = server.CreateObject("ADODB.recordset")       '  There is no need to instatiate this object, the Execute method does that.

Set rs = conn.execute "exec storedProcedureName"       ' rs is an object and therefore requires Set in VB classic.
' If Not (rs.Eof and rs.bof) Then                                        ' The While on its own will cover the case when there are no matching rows.
 while not rs.eof
      Response.write rs("col1") & "<br>"
      Response.write rs("col2") & "<br>"
      Response.write rs("col3") & "<br>"
 wend
'end if
rs.Close                                      ' Don't forget to close it.
Set rs = Nothing                         ' Release the object and recover the memory
cn.Close                                      ' Don't forget to close it.
Set cn = Nothing                         ' Release the object and recover the memory
0
Simplify Your Workload with One Tool

How do you combat today’s intelligent hacker while managing multiple domains and platforms? By simplifying your workload with one tool. With Lunarpages hosting through Plesk Onyx, you can:

Automate SSL generation and installation with two clicks
Experience total server control

 

Author Comment

by:Khanax
ID: 18800148
This is returning the all the code on the webpage.  What am I doing wrong.

Set conn = server.CreateObject("ADODB.Connection")
conn.Open  "PROVIDER=SQLOLEDB;SERVER=local;UID=sa;PWD=;DATABASE=TEST"
'  Set rs = server.CreateObject("ADODB.recordset")       '  There is no need to instatiate this object, the Execute method does that.

Set rs = conn.execute "exec TEST.DBO.NAME_TABLE"       ' rs is an object and therefore requires Set in VB classic.
' If Not (rs.Eof and rs.bof) Then                                        ' The While on its own will cover the case when there are no matching rows.
 while not rs.eof
      Response.write rs("FIRSTNAME") & "<br>"
      Response.write rs("LASTNAME") & "<br>"
 wend
'end if
rs.Close                                      ' Don't forget to close it.
Set rs = Nothing                         ' Release the object and recover the memory
cn.Close                                      ' Don't forget to close it.
Set cn = Nothing                         ' Release the object and recover the memory
0
 

Author Comment

by:Khanax
ID: 18800164
This is what I did
I created a folder called Hello

Placed this folder in wwwroot folder in INETPUB

then I went to the iis manger and click on page to browse.

Thanks
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18800441
Try changing this:
Set rs = conn.execute "exec TEST.DBO.NAME_TABLE"

To:
Set rs = conn.execute "SELECT FIRSTNAME, LASTNAME TEST.DBO.NAME_TABLE WHERE <your condition goes here>"
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 18800447
Let's try that again:
Set rs = conn.execute "SELECT FIRSTNAME, LASTNAME FROM TEST.DBO.NAME_TABLE WHERE <your condition goes here>"
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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

656 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