|
| |
|
|
rikki
Posts: 382
Joined: 4/4/2004
Status: offline
 |
Querying Comma Separated Values - 5/21/2004 14:25:38
I need help getting this query to work.
I have the one field that may have more than 1 value, separated by commas.
I've realized a regular query doesn't work.
I've reviewed another posting and come up with the following.. but it's not working,I'm sure I have at least the syntax wrong.
Thanks:
<%
fp_sQry="SELECT COUNT(*) AS MyCount FROM WETASKIWIN1 WHERE (([DateStart] BETWEEN #::DateStart::# AND #::DateEnd::#) AND (CampSite IN ('" & Replace(strUser,",","','") & "')))"
fp_sDefault="DateStart=&CampSite=&DateEnd="
fp_sNoRecords="<tr><td colspan=3 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=3
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%><!--#include file="../_fpclass/fpdbrgn1.inc"-->
<%
If FP_FieldVal(fp_rs,"MyCount")>0 Then
Response.Redirect "../subheaderfiles/errorpage.htm"
Else
Response.redirect("INFOCampWetaskiwin3.asp?DateStart=" & Request.form("DateStart") & "&DateEnd=" & Request.form("DateEnd")& "&CampSite=" & Request.form("CampSite")& "&CAMP=" & Request.form("CAMP"))
End if
%>
|
|
|
|
rdouglass
Posts: 9202
From: Biddeford, ME USA
Status: offline
|
RE: Querying Comma Separated Values - 5/21/2004 15:45:23
quote:
... AND (CampSite IN ('" & Replace(strUser,",","','") & "')))"
That part looks OK to me at first glance. Is there an error message or anything? Have you confirmed there should be valid records returned from the DB?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
rikki
Posts: 382
Joined: 4/4/2004
Status: offline
|
RE: Querying Comma Separated Values - 5/21/2004 16:22:56
No and Yes
No error message... you are just forwarded to the next page...
Yes it should have been caught in the database...
I'll double check that...
Thanks
Sarah
| |
|
|
|
rikki
Posts: 382
Joined: 4/4/2004
Status: offline
|
RE: Querying Comma Separated Values - 5/22/2004 10:25:55
No strUser is not a variable....
This is what I get back...
SELECT COUNT(*) AS MyCount FROM WETASKIWIN1 WHERE (([DateStart] BETWEEN #::DateStart::# AND #::DateEnd::#) AND (CampSite IN ('')))
I guess the strUser should be a variable?
One of the variables is Lodge so I would replace with Lodge (or is is strLodge)?
Thanks
| |
|
|
|
rikki
Posts: 382
Joined: 4/4/2004
Status: offline
|
RE: Querying Comma Separated Values - 5/22/2004 22:49:37
Hi
Yes - I'm posting from a form to this page/query.
The Field "CampSite" could have up to 15 different values all separated by a comma.
If I understand what I took from this other posting, by putting this statement in it would look at every value that might be in the CampSite field. (all are text)
(CampSite IN ('" & Replace(strLodge,",","','") & "')
So, Lodge is one of the option values...
The other person had strUser...
I am probably totally out to lunch... Learned a lot lately thanks to this forum,... but have a long road ahead of me!
I need this to work... It would simplify my life incredibly!
| |
|
|
|
rikki
Posts: 382
Joined: 4/4/2004
Status: offline
|
RE: Querying Comma Separated Values - 5/24/2004 22:54:54
I response.wrote sql... see below..
Is that what you needed?
SELECT COUNT(*) AS MyCount FROM WETASKIWIN1 WHERE ((DateStart BETWEEN #::DateStart::# And #::DateEnd::#) OR (DateEnd BETWEEN #::DateStart::# AND #::DateEnd::#)) AND (CampSite IN (''))
| |
|
|
|
rikki
Posts: 382
Joined: 4/4/2004
Status: offline
|
RE: Querying Comma Separated Values - 5/25/2004 9:27:36
OK... here's an attempt at explaining...
The user is booking a campsite...
Page 1... They select a Site (checkboxes each with same field name but different values) and enter a date...
Sends to Page 2 where the query determines if this is a duplicate.. (this is the code I posted earlier...
(Select Count(*).....)
If entry a duplicate, sends to error page...
If not, sends to rest of form in Page 3... where user enters personal information and then clicks submit...Sends to database.
Hope this makes sense...
| |
|
|
|
rdouglass
Posts: 9202
From: Biddeford, ME USA
Status: offline
|
RE: Querying Comma Separated Values - 5/25/2004 9:36:14
quote:
SELECT COUNT(*) AS MyCount FROM WETASKIWIN1 WHERE ((DateStart BETWEEN #::DateStart::# And #::DateEnd::#) OR (DateEnd BETWEEN #::DateStart::# AND #::DateEnd::#)) AND (CampSite IN (''))
Nope, that's not what I was looking for. I was looking for actual values that were being used; not hand-coded, but values being passed from the form posting to this page.
I don't want to confuse, but I just wanted to verify we're actually sending good data to a good query.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
rikki
Posts: 382
Joined: 4/4/2004
Status: offline
|
RE: Querying Comma Separated Values - 5/25/2004 9:49:34
For example...
I tried to send this...
CampSite = "Lodge, Bunkhouse, Potlatch"
DateStart = July 5/04
DateEnd = July 5/04
This should have been rejected because the one of those sites was already booked...
The CampSite Fields are checkboxes in the form...
Those values are actually in the order from 1st to the 3rd... But if I was to select just the "Bunkhouse" it is still allowing that through.
| |
|
|
|
BeTheBall
Posts: 6355
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Querying Comma Separated Values - 5/25/2004 10:54:01
To elaborate further on her issue, for which sadly I don't have solution. The Field in the db also contains a comma-delimited string. So in the above example, there may be a record in the db for 7/5/4 where the value for Campsite is Lodge, Bunkhouse, Potlatch. A new user comes along and decides to book just Bunkhouse for the same date. The application allows it because it cannot tell that Bunkhouse is already booked given that the value in the db is Lodge, Bunkhouse, Potlatch. The problem as I see it is users are allowed to book multiple sites with one form submission so you often end up with a SQL where the WHERE clause is trying to compare 2 comma-delimited strings. For example, it is possible that the generated SQL could be something like:
SELECT COUNT(*) AS MyCount FROM WETASKIWIN1 WHERE ((DateStart BETWEEN #6/1/2004::# And #::6/4/2004::#) OR BETWEEN #6/1/2004::# And #::6/4/2004::#)) AND (Bunkhouse, Tent1 IN ('Lodge', 'Tent1'))
As I understand it, that is where the problem lies.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
BeTheBall
Posts: 6355
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Querying Comma Separated Values - 5/25/2004 12:01:25
Here is something you may want to give a try. Go back to your input form and instead of naming all the checkboxes "Campsite", name them sequentially, like Campsite1, Campsite2, Campsite3, etc. Keep the values the same. Then you could redo your Insert SQL so that instead of inserting one record where the campsite value is say, Lodge, Bunkhouse, Tent1, you could insert 3 records each having only one value for Campsite. It will require some custom coding for the Insert but I believe it will then make it so you can have better success at checking for duplicates because you will not be trying to compare two comma-delimited strings. What do you think?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
rikki
Posts: 382
Joined: 4/4/2004
Status: offline
|
RE: Querying Comma Separated Values - 5/25/2004 22:19:14
I'm trying to understand what your suggesting.. It sounds interesting. I'll sleep on it for now!
Thanks
Sarah
| |
|
|
|
BeTheBall
Posts: 6355
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Querying Comma Separated Values - 5/26/2004 9:21:53
Before you change too much, you should do what rdouglass suggests. To write out the SQL that is being generated, go to the page that has this code:
<%
fp_sQry="SELECT COUNT(*) AS MyCount FROM WETASKIWIN1 WHERE (([DateStart] BETWEEN #::DateStart::# AND #::DateEnd::#) AND (CampSite IN ('" & Replace(strUser,",","','") & "')))"
fp_sDefault="DateStart=&CampSite=&DateEnd="
fp_sNoRecords="<tr><td colspan=3 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=3
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%><!--#include file="../_fpclass/fpdbrgn1.inc"-->
<%
If FP_FieldVal(fp_rs,"MyCount")>0 Then
Response.Redirect "../subheaderfiles/errorpage.htm"
Else
Response.redirect("INFOCampWetaskiwin3.asp?DateStart=" & Request.form("DateStart") & "&DateEnd=" & Request.form("DateEnd")& "&CampSite=" & Request.form("CampSite")& "&CAMP=" & Request.form("CAMP"))
End if
%>
Then right after the tag that looks like this <body>, insert this:
<p><%=fp_sQry%></p>
Save the page and then try and insert a record that you know the code will let through. When you do the SQL statement will be written to the top of the resulting page. That is what rdouglass wants to see.
Does that help?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
rikki
Posts: 382
Joined: 4/4/2004
Status: offline
|
RE: Querying Comma Separated Values - 5/26/2004 14:06:14
I'm not getting the results... Where have I got this wrong..
This was only to return the values being sent..
Thanks
Sarah
<!--#include file="../_fpclass/fpdblib.inc"-->
<%
fp_sQry="SELECT COUNT(*) AS MyCount FROM WETASKIWIN1 WHERE (([DateStart] BETWEEN #::DateStart::# AND #::DateEnd::#) AND (CampSite IN ('" & Replace(strUser,",","','") & "')))"
fp_sDefault="DateStart=&CampSite=&DateEnd="
fp_sNoRecords="<tr><td colspan=3 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=3
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%><!--#include file="../_fpclass/fpdbrgn1.inc"-->
<%
If FP_FieldVal(fp_rs,"MyCount")>0 Then
Response.Redirect "../subheaderfiles/errorpage.htm"
Else
Response.redirect("INFOCampWetaskiwin3.asp?DateStart=" & Request.form("DateStart") & "&DateEnd=" & Request.form("DateEnd")& "&CampSite=" & Request.form ("CampSite")& "&CAMP=" & Request.form("CAMP"))
End if
%>
<p><%=fp_sQry%></p>
<input type=hidden name=CAMP value="Camp Wetaskiwin">
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
| |
|
|
|
BeTheBall
Posts: 6355
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Querying Comma Separated Values - 5/26/2004 15:03:35
Sorry I forgot that the user gets redirected to either one page or the other. You will need to comment out the redirect code to be able to see the SQL, like this:
<%
' If FP_FieldVal(fp_rs,"MyCount")>0 Then
' Response.Redirect "../subheaderfiles/errorpage.htm"
' Else
' Response.redirect("INFOCampWetaskiwin3.asp?DateStart=" & Request.form("DateStart") & "&DateEnd=" & ' Request.form("DateEnd")& "&CampSite=" & Request.form ("CampSite")& "&CAMP=" & Request.form("CAMP"))
' End if
%>
Once you do that then I think the SQL string will get written to the page.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
rikki
Posts: 382
Joined: 4/4/2004
Status: offline
|
RE: Querying Comma Separated Values - 5/26/2004 15:14:30
Worked like a charm... Here's the code it produced...
SELECT COUNT(*) AS MyCount FROM WETASKIWIN1 WHERE (([DateStart] BETWEEN #dec 12/06# AND #dec 12/06#) AND (CampSite IN ('')))
| |
|
|
|
BeTheBall
Posts: 6355
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Querying Comma Separated Values - 5/26/2004 15:21:45
Now change the SQL as shown below and try again:
fp_sQry="SELECT COUNT(*) AS MyCount FROM WETASKIWIN1 WHERE (([DateStart] BETWEEN #::DateStart::# AND #::DateEnd::#) AND (CampSite IN ('" & Replace(Request.Form("CampSite"),",","','") & "')))"
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
rikki
Posts: 382
Joined: 4/4/2004
Status: offline
|
RE: Querying Comma Separated Values - 5/26/2004 19:30:19
Well, I tried it... And it sort of worked...
What happened was as long as the first value in a string was selected it would catch a duplicate...
Example The sites Lodge, Bunkhouse, Potlatch are booked for June 12/04.
I tried to book Bunkhouse for June 12/04 and it let it through.. but should have caught it as duplicate....
Any further ideas! I really appreciate all your help!!!
| |
|
|
|
BeTheBall
Posts: 6355
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Querying Comma Separated Values - 5/26/2004 20:00:48
Yea, I didn't think what I was proposing would fix it. I want you to leave the redirect code commented out and response.write the above SQL.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
rikki
Posts: 382
Joined: 4/4/2004
Status: offline
|
RE: Querying Comma Separated Values - 5/26/2004 20:57:11
This is what I got back...
SELECT COUNT(*) AS MyCount FROM WETASKIWIN1 WHERE (([DateStart] BETWEEN #dec 12/04# AND #dec 12/04#) AND (CampSite IN ('Bunkhouse',' Potlatch')))
| |
|
|
|
rikki
Posts: 382
Joined: 4/4/2004
Status: offline
|
RE: Querying Comma Separated Values - 5/28/2004 17:38:26
Need some advice...
Should I try to work the GetString concept that rdouglass suggested... or pack it in...
I don't know anything about how to write in GetString.... ??
Thanks
| |
|
|
|
BeTheBall
Posts: 6355
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Querying Comma Separated Values - 5/28/2004 17:57:38
I can only speak for myself. As I mentioned in another post, I would change the approach. First, I would change the code so that an individual record is created for each campsite chosen instead of creating a comma-delimited string in the CampSite field. Second, I would have the user begin by typing the dates they wish to reserve and then on the next page display your form along with a message such as "The following sites are not available for the date(s) you requested" followed by a list of unavailable sites. You could then cause the check boxes for the reserved sites to be disabled so that the user could not include those in the request. I can help you through that process.
quote:
pack it in...
That is never an option!
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
rikki
Posts: 382
Joined: 4/4/2004
Status: offline
|
RE: Querying Comma Separated Values - 5/28/2004 20:01:00
Well, I'm a trooper or I"m insane but here goes my attempt..
The first page enter date
That page sends to this query..
Which will determine if that date is available.
What I'm not sure how to do is after it runs the query what do I put in the last statement to tell it to go to the next page (which will list the dates select plus the sites available..
<!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM WETASKIWIN1 WHERE ([DateStart] Between #::DateStart::# And #::DateEnd::#)"
fp_sDefault=""
fp_sNoRecords=""
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=4
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%><!--#include file="../_fpclass/fpdbrgn1.inc"-->
<%
If FP_FieldVal(fp_rs,"DateStart") Is Null Then
Response.redirect("INFOCampWetaskiwin_copy(3).asp?DateStart=" & Request.form("DateStart") & "&DateEnd=" & Request.form("DateEnd"))
End if
%><!--#include file="../_fpclass/fpdbrgn2.inc"-->
< Message edited by rikki -- 5/28/2004 20:07:39 >
| |
|
|
|
BeTheBall
Posts: 6355
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Querying Comma Separated Values - 5/28/2004 22:03:00
Let's go with some pure ASP as I am not sure I can get the DRW to do what I want. So have the form where the user enters the dates submit to a new page. Call it ReservationForm.asp. Create a new page. At the top, insert this code:
<%
Dim conntemp, myDSN, myRS, mySQL, arrCampSite
Set conntemp=Server.CreateObject("ADODB.Connection")
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("/trishores-scouts/fpdb/NameofyourDB.mdb")
conntemp.open myDSN
mySQL = "SELECT CampSite FROM WETASKIWIN1 WHERE ([DateStart] Between #::DateStart::# And #::DateEnd::#) OR ([DateEnd] Between #::DateStart::# And #::DateEnd::#) "
Set myRS = Server.CreateObject("ADODB.Recordset")
myRS.Open mySQL, conntemp, 0, 1
If myRS.eof OR myRS.bof then
RecordFound=0
Else
arrCampSite = myRS.getrows()
RecordFound=-1
End if
myRS.Close
Set myRS = nothing
conntemp.Close
Set conntemp = nothing
%>
Then, after the <body> tag, insert this:
The following campsite(s) is/are reserved for the day(s) you selected:<br><br>
<%
If RecordFound then
For Each i in arrCampSite
Response.Write i & "<br>"
Next
else
Response.Write("All sites are available")
End if
%>
Then save the page as ReservationForm.asp. Let's take it to there. Test the page by entering dates where you know there are reservations in the db. When you do, you should get a list of all reserved campsites. Then enter dates where you know all sites are available, when you do you should see "All sites are available". Once we get that working, we will move on.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
rikki
Posts: 382
Joined: 4/4/2004
Status: offline
|
RE: Querying Comma Separated Values - 5/29/2004 8:57:53
I got it to work using DRW...
The code returns booked dates.
Using the above code I get this error..
Microsoft JET Database Engine error '80040e07'
Syntax error in date in query expression '([DateStart] Between #::DateStart::# And #::DateEnd::#) OR ([DateEnd] Between #::DateStart::# And #::DateEnd::#)'.
/campbookings1/1Wetaskiwin/ReservationForm2.asp, line 8
This is the code as I put it on the page the query page goes to...
<%
Dim conntemp, myDSN, myRS, mySQL, arrCampSite
Set conntemp=Server.CreateObject("ADODB.Connection")
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("../fpdb/index.mdb")
conntemp.open myDSN
mySQL = "SELECT CampSite FROM WETASKIWIN1 WHERE ([DateStart] Between #::DateStart::# And #::DateEnd::#) OR ([DateEnd] Between #::DateStart::# And #::DateEnd::#)"
Set myRS = Server.CreateObject("ADODB.Recordset")
myRS.Open mySQL, conntemp, 0, 1
If myRS.eof OR myRS.bof then
RecordFound=0
Else
arrCampSite = myRS.getrows()
RecordFound=-1
End if
myRS.Close
Set myRS = nothing
conntemp.Close
Set conntemp = nothing
%> <html>
<body>
The following campsite(s) is/are reserved for the day(s) you selected:<br><br>
<%
If RecordFound then
For Each i in arrCampSite
Response.Write i & "<br>"
Next
else
Response.Write("All sites are available")
End if
%>
</body>
</html>
< Message edited by rikki -- 5/29/2004 21:26:23 >
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 
