Naming parameters when calling Stored Procedures 1

[dirkg]

Hi all,

I always use stored procedures to retrieve, update,... data. However the list of parameters can be sometimes quite large. With the current synthax I use, I always have to make sure the paramaters I pass are in the same order as they are stated in the stored procedure. However I do not find this very handy and moreover it would give me more flexibility in adapting the stored procedure if I could easaly name the parameters and hence not need to take the order into account anymore.

The syntax I use now is:

Set objCmd = Server.CreateObject("ADODB.Command&quot
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = "{? = call dbo.stp_PersonList_Update ('" & CStr(strFirst_Name) & "','" & _
CStr(strLast_Name) & "', '" & _
CDate(dteBirthDay) & "', '" & _
CStr(strGender) & "', '" & _
CStr(strAddress) & "', '" & _
CStr(strPostalCode) & "', '" & _
CStr(strCountry) & "', '" & _
CStr(strCity) & "', '" & _
CStr(strCompany) & "', '" & _
CStr(strCompany_Website) & "', '" & _
CStr(strCompany_Activity) & "', '" & _
CStr(strPosition) & "', '" & _
CStr(strPersonal_Website) & "', '" & _
CStr(strEmail) & "', '" & _
CStr(strTelephone) & "', '" & _
CStr(strFax) & "', '" & _
CStr(strMobile) & "', " & _
CInt(intPromotion) & ", '" & _
CStr(strPassword) & "', '" & _
CStr(strRemarks) & "', '" & _
CStr(strDiplomas) & "', " & _
CInt(intSecurityLevel) & ", " & _
CInt(ABS(cBool(binActivated))) & ", '" & _
CStr(strActivationCode) & "', " & _
CLng(lngPersonID) &&quot}"
objCmd.CommandType = adCmdText
objCmd(0).Direction = adParamReturnValue
objCmd.Execute
intRecordCount = objCmd(0)


I wonder there is a synthax possible like:

objCmd.CommandText = "{? = call dbo.stp_PersonList_Update (@Firstname = '" & CStr(strFirst_Name) & "', @LastName ='" & CStr(strLast_Name) & "', '" & _ ....

(This synthax doesn't work, I tried it :-()

Thanks a lot in advance for your help.

Greetz,

Dirk

 

[mwolf00]

I use the lazy way to append parameters......

Set objConn = Server.CreateObject( "ADODB.Connection" )
Set objCmd = Server.CreateObject( "ADODB.Command" )
Set objRS = Server.CreateObject( "ADODB.Recordset" )
objConn.Open Application("connectString")
Set objCmd.ActiveConnection = objConn
objCmd.CommandTimeout = 300



if request.servervariables("REQUEST_METHOD") = "POST" THEN
with objCmd
	.commandText = "sp_rentEstSearch"
	.CommandType = adCmdStoredProc
	.parameters(1) = request("locCode")
	.parameters(2) = request("agCode")
	.parameters(3) = request("abCode")
	.parameters(4) = request("leaseNum")
	.parameters(5) = request("orderBy")		
End With			
	
set objRS = objCmd.Execute()
set objCmd = nothing

-- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[dirkg]

Thanks Mike, apparently you can use text descriptions instead of numbers so that solved my problem:

with objCmd
.commandText = &quot;sp_rentEstSearch&quot;
.CommandType = adCmdStoredProc
.parameters(&quot;@ParameterName1&quot = request(&quot;locCode&quot
.parameters(@ParameterName1) = request(&quot;agCode&quot
End With

Do you have any idea how I can open the recordset with the command object in such a way that paging is possible?

Greetz,

Dirk

 

[mwolf00]

When you say &quot;paging&quot;, what are you trying to accomplish? -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[dirkg]

Divide the output over different pages. When you use the connectionobject to open a recordset you can specify the locktype and cursortype so that paging is possible (e.g. rst.Open strSQL, objConn, adOpenStatic , adLockOptimistic ,adCmdStoredProc) but I don't know how to specify this when using the command object...

 

[mwolf00]

I've never tried paging - I think that the cursor type will be irrelevant in asp since the server sends you a page and the connection ends (therefore the cursor will close). You should start a new thread regarding paging - I would be interested in the discussion... -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[dirkg]

pageing is actually quite easy to implement when you have the recordset opened with the right locks. Check faq333-186 for an example.

Greetz,

Dirk

 

[mwolf00]

The FAQ looks pretty good. I'm still not sure how you would implement it with a stored procedure. Check these out. They might help.

http://www.aspfree.com/asp/paging_sqlserver.asp

http://www.4guysfromrolla.com/webtech/062899-1.shtml

-- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[dirkg]

Thanks again Mike!

The first article helped me. I just have to open the recordset with this command:

rst.open objCmd,, adOpenStatic , adLockOptimistic ,adCmdStoredProc

instead of using

set rst = objCmd.Ececute()

Now it works perfectly.


Greetz,

Dirk

 

[mwolf00]

Awesome! That helps me too! -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com