ASP Database Libraries

[Mayoor]

Does anyone have a decent set of ASP functions which make it easier to interact with databases.

So for instance I would call a function which would connect to a db, execute a stored procedure and return a recordset which I could then work with.

 

[DotNetGnat]

this is what you are looking for...

http://www.stardeveloper.com/articles/display.html?article=2001041801&page=1

-DNG

 

[Mayoor]

I can't see any ASP DB functions on that page.....? He mentioned they would be available but I can't see any.

Any ideas?

 

[DotNetGnat]

try this buddy...

http://www.shiningstar.net/articles/articles/database/datafunctions.asp?ID=AW

-DNG

 

[Mayoor]

Sorry mate, I'm looking for something a bit more comprehensive than that to cater for a fair few scenarios.

For instance:

1) A function which executes a stored proc and then returns the recordset as a 2 dimensional array
2) A function which executes a stored proc and then returns a recordset
2) A function which executes a stored procedure and returns no recordset

e.t.c e.t.c

Any ideas?

 

[Tarwn]

'--- Creates a connection object for use from other functions
Function CreateConnection(connString)
   Set CreateConnection = Server.CreateObject("ADODB.Connection")
   CreateConnection.Open connString
End Function

'--- Executes a Stored Proc or SQL Query and returns a recordset
Function ExecProcToRS(procName, connString)
   Dim eptr_conn, eptr_rs
   Set eptr_conn = CreateConnection(connString)
   Set eptr_rs = eptr_conn.Execute(procName)
   Set ExecProcToRS = eptr_rs
   'should probably close connection and set to nothing, but I believe that will kill the recordset
End Function

'--- Executes a stored proc or SQL Query and returns an array
Function ExecProcToArray(procName, connString)
   Dim t_rs
   Set t_rs = ExecProcToRS(procName, connString)
   ExecProcToArray = t_rs.GetRows()
   Set t_rs = Nothing
End Function

'--- executes a stored proc or query that returns no records
Function ExecProc(procName, connString)
   Dim t_conn
   Set t_conn = CreateConnection(connString)
   t_conn.Execute procName
   Set t_conn = Nothing
End Function

I just wrote those freehand, so I may have overlooked something. The only issues I have with it is that:
1) you have to pass stored procs with a SQL "Execute" command
2) the lack of connection cleanup (however you could access the connection object from the RecordSet.Connection property and force it to close when your done with the recordset)

Usage:

'execute a stored proc into myRS object
Dim myRS
Set myRS = ExecProcToRS("Execute sp_Bob arg1, arg2","my connection string")

'execute a stored proc to myArray
Dim myArray
myArray = ExecProcToArray("Execute sp_Bob arg1, arg2","my connection string")

'execute an SQL query with no return values
ExecProc("UPDATE mytable set a = b","my connection string")

In any case, that is a fairly easy setup that you could expand on...took about 5 minutes or so to write (had to answer the phone).

-T