Querying Comma Separated Values (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

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



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



Spooky -> RE: Querying Comma Separated Values (5/21/2004 17:00:54)

Is "strUser" a valid variable on that page?

What does this look like :

fp_sQry="SELECT COUNT(*) AS MyCount FROM WETASKIWIN1 WHERE (([DateStart] BETWEEN #::DateStart::# AND #::DateEnd::#) AND (CampSite IN ('" & Replace(strUser,",","','") & "')))"

response.write fp_sQry
response.end

fp_sDefault="DateStart=&CampSite=&DateEnd="



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



Spooky -> RE: Querying Comma Separated Values (5/22/2004 15:43:15)

Are you posting to this page from a form? - is that where strUser is coming from? what should that value be?
What does that part of the form look like?



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



rdouglass -> RE: Querying Comma Separated Values (5/24/2004 15:44:54)

Can you Response.write your SQL out with values and post it here? (So we can see what kind of SQL you're sending to your DB engine).

I'm sure there's a way we can get this to work.[8|]



rikki -> 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 (''))



Spooky -> RE: Querying Comma Separated Values (5/24/2004 23:00:25)

I think we can get this to work - however I think we also need to step back a bit and redefine (simply) what you need to do. Theres been a few threads, so Im trying to keep up with where we are :-)

Could you do that for us? Simplify what the inputs are and what you expect the output to be as far as pages and user data goes? It may be the direction we are going is unsuitable for your type of data.



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



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



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



rdouglass -> RE: Querying Comma Separated Values (5/25/2004 13:30:34)

quote:

The Field in the db also contains a comma-delimited string.


Actually that may not be such an issue as you think. Using GetString we can do lots of things to get around that particular issue; at least in terms of grabbing data from the DB. GetString will allow you to delimit with just about any character(s) you wish - great for data that has commas. Then you can dump into array, concatenate, whatever. However, using GetString with DRW is just about out-of-the-question.[:'(]

Haven't really been following this thread too closely but I'm still interested in what the actual SQL is being passed to the db with values..[8|][;)]



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



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



rikki -> 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 -> 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"),",","','") & "')))"



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



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



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



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



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



Page: [1] 2 3   next >   >>

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.15625