Click here to Skip to main content
14,775,288 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have an export page that I am trying to use to download a file created on the fly to the user. In the main page I use a record set to store data returned from a SQL Stored procedure and then dump the contents into a Grid.
I then want to allow the user to save the grid contents as a text file tab delimited. So i redirect them to another page and call the same SQL stored procedure to populate the recordset and write it to a file as a download.
The problem is the parameters (using the exact same code that works on the other page) are not being passed to SQL so my record set comes back empty. If I place default values in the SP the record set is returned and the file is created and downloaded.
set connUBC3=Server.CreateObject("ADODB.Connection") 
        connUBC3.Open  "UBC"	
	    If Err.number <> 0 then
		    TrapError Err.source & ": " & Err.description
	    End If
	
	    set cmdTranByCardNo = Server.CreateObject("ADODB.Command")	
		    set cmdTranByCardNo.ActiveConnection = connUBC3
		    cmdTranByCardNo.CommandText = "Transaction_GetByMerchantIdAndCardNo"
		    cmdTranByCardNo.CommandType = adCmdStoredProc
		    cmdTranByCardNo.Parameters.Add("@MerchantID", SqlDbType.nvarchar)
		    cmdTranByCardNo.Parameters("@MerchantID").Value = vMID

            cmdTranByCardNo.Parameters.Add("@CardNo", SqlDbType.nvarchar)
			cmdTranByCardNo.Parameters("@CardNo").Value = vCardNoString
				'Process results


            set SearchTranByCardNoRS = cmdTranByCardNo.Execute ' @MerchantID = vmid,@CardNo = vcardnostring	
           
            dim icount2 
            icount2 = SearchTranByCardNoRS.RecordCount	
		    If Err.number <> 0 then
			    TrapError Err.source & ": " & Err.description
		    End If

            SearchTranByCardNoRS.MoveFirst        
            if  not SearchTranByCardNoRS.eof then

                headertext = "attachment;filename=GiftTransByDate-" & vMID & fileExt
                Response.AddHeader "Content-Disposition",headertext

                Response.Write "TRANS DATE" & sCh & "TERMINAL NUMBER" & sCh & "LOCATION NAME" & sCh & "CARD NUMBER" & sCh & "TRANS  TYPE" & sCh & "TRANS AMOUNT" & sCh & "REMAINING BALANCE" & sCh & "AUTHORIZATION" & vbCrLf
                SearchTranByDateRS.MoveFirst
                do until SearchTranByCardNoRS.eof
                    Response.Write SearchTranByCardNoRS("TranDate") & sCh & SearchTranByCardNoRS("TerminalID") & sCh & SearchTranByCardNoRS("LocationName") & sCh & SearchTranByCardNoRS("CardNumber") & sCh &  SearchTranByCardNoRS("TranType") & sCh & SearchTranByCardNoRS("TranAmount") & sCh & SearchTranByCardNoRS("RemainingBal") & sCh & SearchTranByCardNoRS("AuthCode") & vbCrLf                          
                    SearchTranByCardNoRS.Movenext
                Loop
                SearchTranByCardNoRS.close
                SearchTranByCardNoRS = nothing
            End if


So why does the exact same lookup work in one page and not the other?
I have the same problem with multiple Stored Procedures where they all work on one page but not this one?

Thanks
Posted

The HTML associated with this page aren't given, but the values of vMID and vCardNoString that are probably missing or not set properly. I think you need to check these first.

Good luck!
   
Comments
Dan_R 15-Mar-12 9:38am
   
Even if I hard code the values in the in the code in place of the variables it will not return the record set. But If I place the same values in the SP as default values it will return the record set and uotput the file
E.F. Nijboer 16-Mar-12 4:19am
   
With the extra info you gave in an answer (It would be better to update the question with that info) I think the problem is you do a cross-page post. For more info have a look here: http://msdn.microsoft.com/en-us/library/ms178139.aspx
There is no HTML on this page it is called from the original page like this

<tr>
    <td width="100%" class="caps" align="left" colspan="9">
        <img border="0" src="images/notepad-icon.jpg" align="absmiddle" width="30" height="30">Export To File  <a href="GiftCard-Export.asp?<%=Request.Querystring%>MID=<%=MIDCode %>&StartDateGC=<%=Date1 %>&EndDateGC=<%=Date2 %>&mode=GiftTransByDate&ExportStyle=tdf" target="_blank">[Tab Delimited] </a> OR  <a href="GiftCards.asp?<%=Request.Querystring%>MID=<%=MIDCode %>&Date1=<%=vDate1 %>&Date2=<%=vDate2 %>&mode=GiftTransByDate& ExportStyle=csv">[Comma Delimited]</a></img>
        <a href="GiftCards.asp?<%=Request.Querystring%>&MID=<%=MIDCode %>&StartDateGC=<%=Date1 %>&EndDateGC=<%=Date2 %>&mode=GiftTransByDate&ExportStyle=excel"><img border="0" src="images/excel_icon.gif" align="absmiddle">Export to Excel</img></a>
    </td>
</tr>
   
The export page did not have the adovb include file

Thanks for your time
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900