SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Other Development Tools
 Parameters not being sent to Stored Proc
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DanR
Starting Member

6 Posts

Posted - 03/15/2012 :  14:43:14  Show Profile  Reply with Quote
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

Edited by - DanR on 03/15/2012 16:29:01

X002548
Not Just a Number

15586 Posts

Posted - 03/15/2012 :  14:50:30  Show Profile  Reply with Quote
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/


Go to Top of Page

DanR
Starting Member

6 Posts

Posted - 03/15/2012 :  15:21:41  Show Profile  Reply with Quote
[quote]Originally posted by X002548

Do you need a trailer single tick mark?

? what do you mean please

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 03/15/2012 :  16:13:08  Show Profile  Reply with Quote
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/



Edited by - X002548 on 03/15/2012 16:14:11
Go to Top of Page

DanR
Starting Member

6 Posts

Posted - 03/15/2012 :  16:26:53  Show Profile  Reply with Quote
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

Edited by - DanR on 03/15/2012 16:29:36
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 03/15/2012 :  16:42:34  Show Profile  Visit russell's Homepage  Reply with Quote

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


Where is SearchTranByDateRS defined?

What is the result of the changes I made above?

Edited by - russell on 03/15/2012 16:43:26
Go to Top of Page

DanR
Starting Member

6 Posts

Posted - 03/15/2012 :  17:05:49  Show Profile  Reply with Quote
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?
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 03/16/2012 :  12:23:36  Show Profile  Visit russell's Homepage  Reply with Quote
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

...

Edited by - russell on 03/16/2012 12:31:04
Go to Top of Page

DanR
Starting Member

6 Posts

Posted - 03/16/2012 :  13:34:13  Show Profile  Reply with Quote
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.
Go to Top of Page

DanR
Starting Member

6 Posts

Posted - 03/16/2012 :  16:19:27  Show Profile  Reply with Quote
quote:
Originally posted by russell

is ADOVBS included?



This was the problem.
Thanks for your time and effort
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 03/16/2012 :  20:01:14  Show Profile  Visit russell's Homepage  Reply with Quote
Awesome. Glad you got it nailed!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000