Parameters not being sent to Stored Proc

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

Development Tools

Other Development Tools

Parameters not being sent to Stored Proc

Author

Topic

DanR
Starting Member

6 Posts

Posted - 2012-03-15 : 14:43:14

I have a classic ASP page that calls a stored procedure and prints it to a comma delimited file and initiates the download for the user. The problem I am having is the parameters are not making it to the SP. I have tested the variable values are correct & populated when being called & even replaced them with the actual values in the call. My record set comes back empty each time.

When I added the Default values to my parameters in SQL in the actual SP it will return those results to the page correctly and print to the file.

So somewhere between this call and SQL the parameters are not making it.
Additionally This exact code is used on another page where I place the data in a grid object and it works fine.

Any help is appreciated.


        set connUBC2=Server.CreateObject("ADODB.Connection") 
        connUBC2.Open "UBC"
        If Err.number <> 0 then
		    TrapError Err.source & ": " & Err.description
	    End If
	
        set cmdTranByDate = Server.CreateObject("ADODB.Command")
 
        With cmdTranByDate
	   	    .ActiveConnection = connUBC2
		    .CommandText =  "Transaction_GetByMerchantIdAndInRange" 
	        .CommandType = adCmdStoredProc
            .Parameters.Append .CreateParameter ("RETURN_VALUE", adInteger, adParamReturnValue)
	        .Parameters.Append .CreateParameter ("@MerchantId", adVarChar, adParamInput, 20, vMID)
	        .Parameters.Append .CreateParameter ("@StartDATE", adDBTimestamp, adParamInput, , vDate1)
	        .Parameters.Append .CreateParameter ("@EndDATE", adDBTimestamp, adParamInput, , vDate2)
 
	        set SearchTranByDateRS = .Execute 
	        If Err.number <> 0 then
		        TrapError Err.source & ": " & Err.description
	        End If
        End Wit

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-15 : 14:50:30

Do you need a trailer single tick mark?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

DanR
Starting Member

6 Posts

Posted - 2012-03-15 : 15:21:41

[quote]Originally posted by X002548

Do you need a trailer single tick mark?

? what do you mean please

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-15 : 16:13:08

set SearchTranByDateRS = .Execute '"0015296265","03/08/2012","03/08/2012"'

???

You seem to be missing one '
Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

DanR
Starting Member

6 Posts

Posted - 2012-03-15 : 16:26:53

quote:
Originally posted by X002548

set SearchTranByDateRS = .Execute '"0015296265","03/08/2012","03/08/2012"'

???

You seem to be missing one '
Brett

Actually that is commented out. THis is a classic asp page and that is in the scripting area so the single quote is a comment

The fact is that no matter how I send the multiple parameters they are not getting to the stored Procedure. That line your looking at ends right after the execute the parameters are set above.

I edited the comment out of the original post
Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-03-15 : 16:42:34

[code]
With cmdTranByDate
.ActiveConnection = connUBC2
.CommandText = "Transaction_GetByMerchantIdAndInRange"

Response.Write "@MerchantId = " & vMID & "<br>" & vbCrLf
Response.Write "@StartDATE = " & vDate1 & "<br>" & vbCrLf
Response.Write "@EndDATE = " & vDate2 & "<br>" & vbCrLf

.CommandType = adCmdStoredProc
'.Parameters.Append .CreateParameter ("RETURN_VALUE", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter ("@MerchantId", adVarChar, adParamInput, 20, vMID)
.Parameters.Append .CreateParameter ("@StartDATE", adDBTimestamp, adParamInput, , vDate1)
.Parameters.Append .CreateParameter ("@EndDATE", adDBTimestamp, adParamInput, , vDate2)

set SearchTranByDateRS = .Execute
If Err.number <> 0 then
TrapError Err.source & ": " & Err.description
End If
End With[/code]

Where is SearchTranByDateRS defined?

What is the result of the changes I made above?

DanR
Starting Member

6 Posts

Posted - 2012-03-15 : 17:05:49

When I use the response write to file I can see all my variables are populated with the correct values. I have also tried with the variables removed and passing the string values to the SP.
I get the same results.

Where is SearchTranByDateRS defined?
on the line that says:

set SearchTranByDateRS = .Execute

No where else. Is that wrong?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-03-16 : 12:23:36

is ADOVBS included?

Sizes are missing on your datetimes. Try putting 8 in there.

Also, comment out the error handler. You WANT to see the actual error and let the page crash while testing.

If none of those work, try this:


Dim cmd
Dim rs

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

With cmd
	.ActiveConnection = "UBC"
	.CommandType = 4 '' adCmdStoredProc
	.CommandText = "Transaction_GetByMerchantIdAndInRange"

	.Parameters.Append .CreateParameter ("@MerchantId", 200, 1, 20, vMID)
	.Parameters.Append .CreateParameter ("@StartDATE", 135, 1, 8, vDate1)
	.Parameters.Append .CreateParameter ("@EndDATE", 135, 1, 8, vDate2)

	rs.Open .Execute
End With

...

DanR
Starting Member

6 Posts

Posted - 2012-03-16 : 13:34:13

I tried both suggestions and in both cases I got this error
This webpage is not found
No webpage was found for the web address: https://ims.WebSite.net/GiftCard-export.asp?MID=0015296265&StartDateGC=03/08/2012&EndDateGC=03/08/2012&mode=GiftTransByDate&ExportStyle=tdf
Error 6 (net::ERR_FILE_NOT_FOUND): The file or directory could not be found.

Which is what I got before.

DanR
Starting Member

6 Posts

Posted - 2012-03-16 : 16:19:27

quote:
Originally posted by russell

is ADOVBS included?

This was the problem.
Thanks for your time and effort

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-03-16 : 20:01:14

Awesome. Glad you got it nailed!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources