Hi,

Hopefully this is an easy question - I setup a "query" form on one page and would like to display results on the other. I setup a recordset to pull the data from the forms and display it. What I am confused about is:

1) How to use repeating regions to display multiple rows of data (and display results over more than one page if neccesary)

2) Is the recordset set up correctly (specifically the date range part)

3) Since this is a query - should I use OR instead of AND - I have the default value as being *.

Here is the SQL code for the recordset: (without the variables)

SELECT *
FROM tblsamples
WHERE Date_Stored BETWEEN #varstorefrom# AND #varstoreto# AND Retired_Date BETWEEN #varretiredfrom# AND #varretiredto# AND Freezer='varfreezer' AND Tower='vartower' AND Box='varbox' AND UPN='varupn' AND UPNA='varupna' AND Person_Storing='varperson'
UNION ALL
SELECT *
FROM tblretired
WHERE Date_Stored BETWEEN #varstorefrom# AND #varstoreto# AND Retired_Date BETWEEN #varretiredfrom# AND #varretiredto# AND Freezer='varfreezer' AND Tower='vartower' AND Box='varbox' AND UPN='varupn' AND UPNA='varupna' AND Person_Storing='varperson'

Thanks for looking,

Chris

ANOTHER EDIT:

I have been working on my SQL statemtn most of the day - I am hung up on something - I changed and AND to a OR - and immediately was able to get data - however it was either all the data or just three records! (depending on were I put the OR) If I return the SQL to all AND - no data!

I feel that it should look for the specified criteria and post only those records - I have the results page setup perfectly - I just need to work this annoying sql statment

Here is the most up-to-date SQL:

SELECT * FROM tblsamples WHERE Date_Stored BETWEEN #varstorefrom# AND #varstoreto# AND Freezer='varfreezer' AND Tower='vartower' AND Box='varbox' AND UPN='varupn' AND UPNA='varupna' AND Person_Storing='varperson'
UNION ALL
SELECT *
FROM tblretired
WHERE Retired_Date BETWEEN #varretiredfrom# AND #varretiredto# AND Date_Stored BETWEEN #varstorefrom# AND #varstoreto# AND Freezer='varfreezer' AND Tower='vartower' AND Box='varbox' AND UPN='varupn' AND UPNA='varupna' AND Person_Storing='varperson'

These two tables are exactly the same - however tblsample does not have data in the retired fields. Any help would be appreciated,

Chris

It's hard for me to judge whether those queries are right as I don't know your table structure and database.

When I need to debug queries like these, I use Response.Write to dump them to the screen and then debug them in Access or Sql Server. It's much easier to use those tools for debugging as they often give more useful error messages.

Few tips: wrap the BETWEEN statement in parentheses, make sure your dates don't include time or that the min value contains 00:00 and the max value 23:59. Otherwise, between won't work.

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: 02 - You Think I Ain't Worth A Dollar, But I Feel Like A Millionaire by Queens of the Stone Age (Track 2 from the album: Songs For The Deaf) What's This?

Hi Imar,

Sorry to be bothering you again - My SQL statement seems to be good (I modified to only select one table). The problem is that it won't accept * for a value (MS Access 2003) - if I put in data that matches perfectly to data that is in the database - it will retunr the results. However, if I try to select all - no records will be returned. It is like the query stops and won't drill down deeper. In addition, I can't even slect multiples - it just won't return anything!

Here is the new SQL statement:

SELECT Freezer, Tower, Box, Location, UPN, UPNA, Date_Drawn, Date_Stored, Person_Storing, Total_Vials, Freezing_Method, Comments
FROM tblsamples
WHERE (Date_Stored BETWEEN #varstorefrom# AND #varstoreto#) AND Tower='vartower' AND Box='varbox' AND UPN='varupn' AND UPNA='varupna'

I am using the asterik as my wildcard (which I am pretty confident is the correct wildcard) The date field is correct (short date format MM/DD/YYYY) In fact when I was bug fixing - I was able to search by date just fine (it was the only variable) Any ideas - I have tried using access to run the query - but I am not to good at using its query builder.

Thanks,

Chris

First of all, I don't see an asterisk. Instead of writing out the source of the query, it's probably easier to write it out after it has been filled with the true values.

Secondly, the Access query designer can be critical in these kind of tasks. It's not that hard; you don't need to use the query designer at all. All you need to do is create a new query and then switch to Sql view and paste in your query. Access will tell you what's wrong.
Instead of the # you could also try an ' and instead of a * you could try %

Various flavors of Access and ADO behave differently in that respect. Can't remember right now what works and what doesn't.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: 50ft Queenie by P.J. Harvey (Track 8 from the album: Rid Of Me) What's This?

Hi Imar,

I was able to use the below criteria to pull data via access. do you know how to translate this into dreamweaver? I tried to add paranthesis around the individual columns but I am having server problems - so I am not sure if it is working again. Should I add LIKE next to the variable I am using? Anyway - I appreciate your help!

Here is the code:

SELECT tblsamples.Freezer, tblsamples.Tower, tblsamples.Box, tblsamples.Location, tblsamples.UPN, tblsamples.UPNA, tblsamples.Date_Drawn, tblsamples.Date_Stored, tblsamples.Person_Storing, tblsamples.Total_Vials, tblsamples.Freezing_Method, tblsamples.Comments
FROM tblsamples
WHERE (((tblsamples.Tower) Like "*") AND ((tblsamples.Box) Like "*") AND ((tblsamples.UPN) Like "*") AND ((tblsamples.UPNA) Like "*") AND ((tblsamples.Date_Stored) Between #6/15/2005# And #6/24/2005#));

thanks,
Chris

If I were you, I'd use this query directly to try it out. E.g.:

sql = The Stuff You Just Posted
Dim myConnection
Dim myRecordset
Set myConnection = Server.CreateObject("ADODB.Connection")
' open the connection here with your connection string
Set myRecordset = myConnection.Execute(sql)
If myRecordset.EOF Then
  Response.Write("No records found")
Else
  Response.Write("Records found")
End If

If that works, you know your query works. From there you can try to make the query dynamic.
If the query doesn't work, replace the * with % and the # with '
See if that works.....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Hi Imar, I probably haven't been very clear (or I still don't know what I need!) I am trying to make the query dynamic and I can't figure that out - Here is the actual Dreamweaver code (as opposed to the stuff I was sending you earlier) (Incidently - when I was testing the access sql statements I kept getting "data type mismatch" errors) I am not asking you to fix this - just point me to where I am going wrong!! Thanks a bunch.

<%
Dim rssample__varstorefrom
rssample__varstorefrom = "01/01/2000"
If (Request.Form("date_storefrom") <> "") Then
  rssample__varstorefrom = Request.Form("date_storefrom")
End If
%>
<%
Dim rssample__varstoreto
rssample__varstoreto = "06/24/2005"
If (Request.Form("date_storeto") <> "") Then
  rssample__varstoreto = Request.Form("date_storeto")
End If
%>
<%
Dim rssample__varbox
rssample__varbox = "*"
If (Request.Form("box") <> "") Then
  rssample__varbox = Request.Form("box")
End If
%>
<%
Dim rssample__vartower
rssample__vartower = "*"
If (Request.Form("tower") <> "") Then
  rssample__vartower = Request.Form("tower")
End If
%>
<%
Dim rssample__varupn
rssample__varupn = "*"
If (Request.Form("upn") <> "") Then
  rssample__varupn = Request.Form("upn")
End If
%>
<%
Dim rssample__varupna
rssample__varupna = "*"
If (Request.Form("upna") <> "") Then
  rssample__varupna = Request.Form("upna")
End If
%>
<%
Dim rssample
Dim rssample_numRows

Set rssample = Server.CreateObject("ADODB.Recordset")
rssample.ActiveConnection = MM_sample_tracking_STRING
rssample.Source = "SELECT Freezer, Tower, Box, Location, UPN, UPNA, Date_Drawn, Date_Stored, Person_Storing, Total_Vials, Freezing_Method, Comments FROM tblsamples WHERE (Date_Stored BETWEEN #" + Replace(rssample__varstorefrom, "'", "''") + "# AND #" + Replace(rssample__varstoreto, "'", "''") + "#) AND Tower='" + Replace(rssample__vartower, "'", "''") + "' AND Box='" + Replace(rssample__varbox, "'", "''") + "' AND UPN='" + Replace(rssample__varupn, "'", "''") + "' AND UPNA='" + Replace(rssample__varupna, "'", "''") + "'"
rssample.CursorType = 0
rssample.CursorLocation = 2
rssample.LockType = 1
rssample.Open()

rssample_numRows = 0
%>

I was trying to point you in the right direction.

There are basically three types of queries (or actually code for the queries):

1. The static query you posted earlier that worked in Access

2. The dynamic ASP code that builds up the query at run-time

3. The resulting Sql statement created in step 2

From the code you posted for step 2, I cannot see how step 3 will end up. Maybe variables are missing, maybe you're passing invalid values, etc. Who knows.

So, my suggestion is to examine the *results* of step 2. Use something like this:

rssample.Source = "SELECT Freezer, bla bla bla"
Response.Write("SQL is " & rssample.Source)
Response.End

This results in the entire sql statement with the values being printed on screen. Run that in access and see where it fails. Post it to this list if you want me to look at it. But please realize I don't have your database, so I cannot judge stuff like column types.

Did you try to work with % and '?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

HI Imar,

I pretty much have it now - made up a dynamic query - the code is ugly -(I modified DW code) but it is working:

I have one problem though - and you helped me with something like it already - but here it is. If on the form page I select multiple in any of the columns - I will not return any records. BAsically, I could search lets say "tower" which has 1 - 44 - if I were to select 1, 2, 3 I would not return any data - the sql string shows 1, 2, 3 in it - but it is like the query stops. Here is my current processing code:

<%
Dim sqlstr
Dim rssample__vartower
Dim rssample__varstorefrom
Dim rssample__varstoreto

%>
<%
rssample__varstorefrom = "01/01/2000"
If (Request.Form("date_storefrom") <> "") Then
  rssample__varstorefrom = Request.Form("date_storefrom")
End If
%>
<%
rssample__varstoreto = "01/01/2010"
If (Request.Form("date_storeto") <> "") Then
  rssample__varstoreto = Request.Form("date_storeto")
End If
%>
<%
sqlstr = "SELECT * FROM tblsamples WHERE Date_Stored BETWEEN #" + rssample__varstorefrom + "# AND #" + rssample__varstoreto + "#"

rssample__vartower = "*"
If (Request.Form("tower") <> "") Then
  rssample__vartower = Request.Form("tower")
    If rssample__vartower <> "*" then
        sqlstr= sqlstr + " and Tower='" + rssample__vartower + "' "
    End if
End If
%>
<%
Dim rssample__varbox
rssample__varbox = "*"
If (Request.Form("box") <> "") Then
  rssample__varbox = Request.Form("box")
    If rssample__varbox <> "*" then
        sqlstr= sqlstr + " and box='" + rssample__varbox + "' "
    End if
End If
%>
<%
Dim rssample__varupn
rssample__varupn = "*"
If (Request.Form("upn") <> "") Then
      rssample__varupn = Request.Form("upn")
    If rssample__varupn <> "*" then
        sqlstr= sqlstr + " and upn='" + rssample__varupn + "' "
    End if
End If
%>
<%
Dim rssample__varupna
rssample__varupna = "*"
If (Request.Form("upna") <> "") Then
  rssample__varupna = Request.Form("upna")
    If rssample__varupna <> "*" then
        sqlstr=sqlstr + " and upna='" + rssample__varupna + "' "
    End if
End If
%>
<%
Dim rssample
Dim rssample_numRows

Set rssample = Server.CreateObject("ADODB.Recordset")
rssample.ActiveConnection = MM_sample_tracking_STRING
rssample.Source = sqlstr
rssample.CursorType = 0
rssample.CursorLocation = 2
rssample.LockType = 1
rssample.Open()

rssample_numRows = 0
'Response.Write("SQL is " & rssample.Source)
'Response.End

And here is the sql output for the hypothetical situation above:

SQL is SELECT * FROM tblsamples WHERE Date_Stored BETWEEN #01/01/2005# AND #06/27/2005# and Tower='01, 02, 03'

I guess any help would be great - I had been thinking about using something like this before rssample.open:

  For Each location In Request.Form("tower")

  Next

Thanks for your time - it actually helps me a lot when you make me dig - but it sure is frustrating!!

Chris