recordset(?) problem 1

[bdc1]

I am working on a set of pages where a user searches for records over a range of dates, and then they can select a sub-set of those records to be exported to excel.

So far I have the date range search set up on one page - I'll call it search.asp. It's pretty simple - a form with two text boxes and a submit button. The values from the test boxes are passed to two session variables.

The second page (list.asp) returns the matching records. I'm doing this in DreamweaverMX and am using the "repeat region" server behavior to display all of the records in a table. This all works just fine.

The table with the records is inside a form. (The action property is set to "") There is a checkbox that is used to indicate each record that should be exported. The value of this field is set to the recordID for each record that is checked.

There is also a submit button and it is here that I am running into problems. No matter what I try to have happen when the submit button is clicked, I always get an error in the SQL statement that defines the recordset that is being already being displayed.

Here is the code for the recordset (which works when list.asp is first loaded):

<%
Dim SearchByDate
Dim SearchByDate_numRows

Set SearchByDate = Server.CreateObject("ADODB.Recordset")
SearchByDate.ActiveConnection = MM_DERC_STRING
SearchByDate.Source = "SELECT dtSubmitted, dtCompleted, numRequest, numSamples, txtLabMember, txtPI_name, txtServiceAbbr, ynExport  FROM qryMolSeq_QryDate  WHERE dtSubmitted Between #" + Session("Dt1") + "# AND #" + Session("Dt2") +"#  ORDER BY numRequest ASC"
SearchByDate.CursorType = 0
SearchByDate.CursorLocation = 2
SearchByDate.LockType = 1
SearchByDate.Open()

SearchByDate_numRows = 0
%>

Right now, all I am doing with the submit button is:

<%If Request("Submit") <> "" Then 
response.redirect("test.asp")
End If %>

(test.asp is just a blank page. Eventually this will be replaced with code that selects the records and then redirects to the excel sheet)

Here is what I get when I try to submit the form -
Microsoft JET Database Engine (0x80040E07)
Syntax error in date in query expression 'dtSubmitted Between ## AND ##'.
/internal/admin_MolSeqQryDate2.asp, line 60


First, it is obviously "losing" the dates, but I'm not sure why since a)this works when the page is loaded and b) I can display the values of the session variables that hold the dates on list.asp as well as on other pages.

Second, why is it trying to do anything with this recordset even in this very oversimplified case where I am just redirecting to another page?

Any help/suggestions would be greatly appreciated!

 

[ChrisHirst]

assuming you are using vbScript your concatenation should be done using "&" rather than "+"

try using

response.redirect...
response.end
end if

Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[bdc1]

I tried both of these suggestions, with no luck.

It's interesting that DreamweaverMX writes the vbscript with + to concatenate instead of &. I'll have to go back and change this on a lot of pages I guess, but right now it is not causing any problems.

 

[TwoOdd]

Check your form line and make sure the action is taking you to the right page. I could be wrong, but looking at the error, it looks like you copied the form information from the previous page and didn't modify the code. Easy thing to miss when you spend your day coding.

Hope this helps,

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[bdc1]

That doesn't seem to be it either as I have action = "". I did this purposefully as I will eventually have more than one button in this form. The submit button that I have currently will ultimately be used to first update records in the database and then redirect to another page. I just put the response.redirect("test.asp") in there as a placeholder until I get this other issue worked out.

Thanks for the suggestion, though - I certainly have done that in the past!

 

[TwoOdd]

It seems like it is still trying to run the code based on the values from your previous page. Is the previous form page submitting directly to list.asp? If so, then when you hit the submit button on list.asp it will refresh list.asp and ask for the variables from your previous form (which now are blank), reset your session variables and then cause the error you are now getting because your session variables are now blank.

For right now, why not just put action="test.asp". I use an intermediate page to decipher what button was pushed, then do a redirect from there. Another suggestion is to use javascript to decipher what button was pushed.

The key here is not to refresh list.asp if list.asp has the code that captures the variables from the previous form.

Hope this helps,

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[DreXor]

also you could attempt to use Server.Transfer <page> instead of Response.Redirect since it's on the same server/domain



DreX
aKa - Robert

 

[Tarwn]

The only thing I can think of is that you didn't put a value in your submit button and it is bypassing he if statement because there is no value. I assume the rediection script is above the database portion.

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
The never-completed website:

http://www.tiernok.com/

 

[bdc1]

TwoOdd gets a star for this because it made me think and look at other things in the code for this page that might be causing the problem.

Sure enough, once again a victim of "recycling" pages developed for other purposes - there was an include file referenced on these pages that prevented the page from being cached. Once I removed this, everything worked.