ADO Stored Procedure VB Script

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ADO Stored Procedure VB Script

Author

Topic

NickyJ
Starting Member

46 Posts

Posted - 2005-08-09 : 11:33:04

Hi,

I'm new to development, I am trying to execute a Stored Procedure via VBScript within ASP page. Code as below, can anyone tell me where I'm going wrong or where I could seek help ???

Stored Proc is a simple 1 input parameter with 1 output parameter.

<%

const adCmdStoredProc=4
const adInteger=3
const adChar=129
const adDate=7
const adParamInput=1
const adParamReturnValue=4

Set cn =CreateObject("ADODB.Connection")
cn.Open "DRIVER={SQL Server};server="lon-nt-sq02";database=pubs;uid=sa;pwd=password"
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn

cmd.CommandText = "MyProc"
cmd.commandtype=adCmdStoredProc

cmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, adParamReturnValue)
cmd.Parameters.Append cmd.CreateParameter("Param1", adInteger, adParamInput)

cmd.Parameters("Param1") = 10

cmd.Execute
Response.write cmd.parameters("RetVal")

%>

Thanks all

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-09 : 12:22:24

Looks like you have a good start. ADO stuff has it's own forum at

http://sqlteam.com/Forums/forum.asp?FORUM_ID=18

There are some good articles on SQLTeam about calling stored procs. Google will find several tutorial sites too:

http://www.google.com/search?hl=en&q=ado+call+stored+procedure&btnG=Google+Search

Return Values return only integer data. OUTPUT parameters can be any data type and are more versatile.

You don't mention the error or problem you are having.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-08-09 : 12:29:37

Are you using output param? This seems to work for me...

<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<!--METADATA TYPE="TypeLib" NAME="Microsoft ActiveX Data Objects 2.6 Library" UUID="{00000206-0000-0010-8000-00AA006D2EA4}" VERSION="2.6" -->
</HEAD>
<BODY>

<%
	dim cn, cmd, p, rs, retValue
	
	cn = "Provider=SQLOLEDB;Persist Security Info=False;User ID=webuser; password=webuser;" &_
			"Initial Catalog=pubs;Data Source=bxfl061"

	Set cmd = Server.CreateObject ("ADODB.Command")

	cmd.ActiveConnection = cn
	cmd.CommandText = "MyProc"
	cmd.CommandType = adCmdStoredProc
	
	set p = cmd.Parameters	
		p.Append cmd.CreateParameter("Param1", adInteger, adParamInput)
		p.Append cmd.CreateParameter("RetVal", adInteger, adParamOutput)
	
	cmd("Param1") = 10
	
	Set rs = cmd.Execute 
	
	retValue = cmd.Parameters("RetVal")
	Response.write retValue
	
	
	' clean up
    If rs.State = adStateOpen then
        rs.Close
    End If
    If cmd.State = adStateOpen then
        cmd.Close
    End If
    
    Set rs = Nothing
    Set cmd = Nothing

%>


</BODY>
</HTML>

Nathan Skerl

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-10 : 01:57:39

Refer this also
http://mindsdoor.net/VB/VBExecSPSimple.html

Madhivanan

Failing to plan is Planning to fail

NickyJ
Starting Member

46 Posts

Posted - 2005-08-10 : 06:33:25

Thank you for your assistance people, have amended code and all is working.

NickyJ
Starting Member

46 Posts

Posted - 2005-08-10 : 07:18:08

Now I've got the execution of the Proc to work then I now need to return XML from the Stored Procedure whilst handling input parameters.

I am being passed the following XML from Flash.

<list CntryId="12" JType="0" Keyword="hello" Andor="0" ></list>

CntryID is say Country Code. I need to pass this along with any other fields such as Keyword, JType into the Procedure which then queries the DB tables and then returns the output as XML.

Would I have to import the XML into a table first and strip out the SARG arguments and pass these to the SP as input parameters??

I'm guessing I now need to look into 'sp_xml_preparedocument' and putting this within my Stored Procedure, any further assistance would be greatly appreciated.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources