populating a SQL stored procedure in DB: SQL 2000 & (Access 2000) 1

[gus121]

How do you populate data from an ASP page into a SQL query stored procedure within SQL 2000 and Access 2000?

e.g the selected Autoindexnumber from a table can be used to retrieve just that column of data from a table.

Is it possible?

All help welcome.
Thanks Angus

 

[Palooka]

Yes it's possible. Let's say your stored procedure is called

sp_getMyData

and you have stored the Autoindexnumber i a variable called

intIndex

. Then you could try something like this:

'Always declare variables!
Dim db,rs,strQuery

'We set the objects, to enable intellisense
Set db = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

'The query to run
strQuery = "sp_getMyData " & intIndex

'Run the query
db.Open strConnectionString
Set rs = db.Execute(strQuery)

'Do what you want with the data
'...

'Cleanup
rs.close
db.close
Set rs = Nothing
Set db = Nothing

Hope this helps,
Palooka

 

[gus121]

Yes but how do use this variable in the Stored Procedure to do a specific query search in the DB?

Any help much appreciated.
Gus

 

[Palooka]

Something similar to this:

Create Procedure dbo.sp_GetOneItem(@iItemID integer) 
As
   SELECT * FROM Table
   WHERE ItemID = @iItemID
RETURN (0)

Palooka

 

[gus121]

Thanks got it to work.