Help: stored procedure getting executed twice

[Bill S.]

I am having a problem where a SQL Server stored procedure is being
executed twice by a single cal in an ASP page (Windows 2000).

I run this in Visual InterDev, and verified that the .Execute step
is not happenning twice. I put a statement in the stored procedure
to write the params and timestamp to a log file every time it executes,
and can see this it is happening twice.

This is my first real experience with ASP, so hopefully it is something
simple.

tia,
Bill


The ASP code looks like:

Cnxn.Open strCnxn

If Request.QueryString("mode")="AddUser" Then
If (Trim(Request.Form("txtUserName"))) = "" Then
strError = "Error: Username must be entered"
ElseIf (Trim(Request.Form("txtPassword")) = "") Then
strError = "Error: Password must be entered"
Else
If Cnxn.State = adStateOpen Then
txtUserName = LCase(Trim(Request.Form("txtUserName")))
txtPassword = Trim(Request.Form("txtPassword"))
txtFirstName = Trim(Request.Form("txtFirstName"))
txtLastName = Trim(Request.Form("txtLastName"))

Set cmdAddUser = Server.CreateObject("ADODB.Command")
Set rsAddResult = Server.CreateObject("ADODB.Recordset")

With cmdAddUser
.ActiveConnection = Cnxn
.CommandText = "dmsAddUser"
.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger,
adParamReturnValue)
.Parameters.Append .CreateParameter("@userName", adVarChar,
adParamInput, 15)
.Parameters.Append .CreateParameter("@userPWD", adVarChar,
adParamInput, 15)
.Parameters.Append .CreateParameter("@lname", adVarChar,
adParamInput, 20)
.Parameters.Append .CreateParameter("@fname", adVarChar,
adParamInput, 20)
.Parameters.Append .CreateParameter("@errMsg", adVarChar,
adParamInputOutput, 50)

.Parameters("@userName") = txtUserName
.Parameters("@userPWD") = txtPassword
.Parameters("@lname") = txtLastName
.Parameters("@fname") = txtFirstName
.Parameters("@errMsg") = ""

Set rsAddResult = .Execute
If rsAddResult.Open Then
rsAddResult.Close
End If

strError = .Parameters("@errMsg")
End With

Set cmdAddUser = Nothing
Set rsAddResult = Nothing

[Bob Barrows]

Bill S. wrote:

> Set rsAddResult = .Execute
> If rsAddResult.Open Then
> rsAddResult.Close
> End If

Here's your problem right here. Your stored procedure does not return
records. Why on earth would you create a recordset to execute the procedure?
Simply do this:

..Execute ,,128
strError = .Parameters("@errMsg")


The 128 is the value of the adExecuteNoRecords constant. You should tell ADO
not to bother creating a recordset object to retrieve the results of this
stored procedure.

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"