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

            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
                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 = 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?


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!
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:
There is no HTML on this page it is called from the original page like this

    <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>
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