calling stored procedures in VB and ASP
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.
Web Development
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.
by aksrini_1980
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...
by vajjar
by andrew
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
by vajjar
This discussion is closed to new replies.
Sort by Trending