General discussion

Locked

calling stored procedures in VB and ASP

By vajjar ·
i want to know how to call a SQL server stored procedure in VB using ADo and RDO..
then the same with ASP with passing parameters....if u have code plz send it with explanation.

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

calling stored procedures in VB and ASP

by aksrini_1980 In reply to calling stored procedures ...

Hello! I can help u access a Stored procedure in SQL Server from an ASP file. There are actually two needs to which you can cater to in this way. One is to simply retrieve the resultset from the execution of a SP or u can pass params to the SP and then retrieve the results of it for use in ur ASP file (as u do in functions). The first procedure is pretty simple. I'll give u an example. Assume u have a SP name 'test' U cal execute it as:
Dim Cn 'connection
Dim Rt 'recordset
Set Cn=Server.CreateObject("ADODB.Connecton")
Set Rt=Server.CreateObject("ADODB.Recordset")
Cn = "dsn=dsnname","username","password"
Qry = "exec test"
Set Rt = Cn.execute(Qry)

Now using Rt("..") u can retrieve the result of the SP.
The other md. requires morespace... So next time...

Collapse -

calling stored procedures in VB and ASP

by vajjar In reply to calling stored procedures ...

The question was auto-closed by TechRepublic

Collapse -

calling stored procedures in VB and ASP

by andrew In reply to calling stored procedures ...

I prefer to use a pure SQLOLEDB connection (eliminaitng the ODBC layer) and the ADO Command object. This lets you specify parameters to the stored proc and get return values easily:

Dim oCon As ADODB.Connection
Dim oCmd As ADODB.Command
Dim oRSAs ADODB.Recordset

Set oCon = New ADODB.Connection
Set oCmd = New ADODB.Command
Set oRS = New ADODB.Recordset

oCmd.CommandType = adCmdStoredProc
oCmd.CommandText = "pGetStates"
oCmd.Parameters.Append oCmd.CreateParameter("@ActiveFlag", adBoolean, adParamInput, , iActiveFlag)

oCon.Open "Provider=SQLOLEDB.1; Data Source=ServerName;Initial Catalog=DBName; User ID=sa; Password="

Set oCmd.ActiveConnection = oCon
oRS.CursorLocation = adUseClient
oRS.Open oCmd, , adOpenForwardOnly, adLockBatchOptimistic 'use the open method NOT the cmd.Execute

Set oCmd.ActiveConnection = Nothing 'disconnect

Collapse -

calling stored procedures in VB and ASP

by andrew In reply to calling stored procedures ...

Note that oCmd.CommandText is simply the stored proc name.

Collapse -

calling stored procedures in VB and ASP

by vajjar In reply to calling stored procedures ...

The question was auto-closed by TechRepublic

Collapse -

calling stored procedures in VB and ASP

by vajjar In reply to calling stored procedures ...

This question was auto closed due to inactivity

Back to Web Development Forum
6 total posts (Page 1 of 1)  

Software Forums