using a sp to return data to asp

[nimarii]

hello -

i don't have that much experience working with asp and sql server. currently, i have a page that displays quite a bit of data, and everything is handled within asp. I need to change this to sql so that we can call the sp whenever i need to, and then get the values.

does anyone know how i should approach this?

many thanks...

 

[DotNetGnat]

put all your sql statements inside a stored procedure and call the stored procedure...

for example...lets consider a simple query

sql = "Select field1, field2 From mytable"

so what you do in the ASP page is...

1. 'set rs and conn objects
2. 'open connection using your connection string
3. 'your sql statement here
something like

sql = "Select field1, field2 From mytable"
'then open it...
rs.open sql, conn

4. 'display the record set...something like
do until rs.eof
'blah blah blah
rs.movenext
loop
5. close all your objects...

now using stored procedure you do the following...

in your query analyzer you create the following...

CREATE Procedure myprocedure AS
Select field1, field2
From mytable
Go

then myprocedure will be created....now your sql looks like this...

1. 'set rs and conn objects
2. 'open connection using your connection string
3. 'your sql statement here

[red]sql = "execute mystoredprocedure"[/red]
'then open it...
rs.open sql, conn

4. 'display the record set...something like
do until rs.eof
'blah blah blah
rs.movenext
loop
5. close all your objects...

was that simple enough to understand...

-DNG