| Author |
 Topic  |
|
|
DanR
Starting Member
6 Posts |
 Posted - 03/15/2012 : 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
|
Edited by - DanR on 03/15/2012 16:29:01
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
DanR
Starting Member
6 Posts |
Posted - 03/15/2012 : 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 |
|
|
DanR
Starting Member
6 Posts |
Posted - 03/15/2012 : 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 |
Edited by - DanR on 03/15/2012 16:29:36 |
|
|
|
russell
Pyro-ma-ni-yak
USA
4962 Posts |
Posted - 03/15/2012 : 16:42:34
|
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 |
|
|
|
DanR
Starting Member
6 Posts |
Posted - 03/15/2012 : 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
USA
4962 Posts |
Posted - 03/16/2012 : 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
... |
Edited by - russell on 03/16/2012 12:31:04 |
|
|
|
DanR
Starting Member
6 Posts |
|
|
DanR
Starting Member
6 Posts |
Posted - 03/16/2012 : 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
USA
4962 Posts |
Posted - 03/16/2012 : 20:01:14
|
| Awesome. Glad you got it nailed! |
|
|
| |
Topic |
|
Parameters not being sent to Stored Proc
