|
| |
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
 |
RE: Querying Comma Separated Values - 6/1/2004 23:04:33
Looks OK. Did you see my post just above the one where you posted the code for the form? There are some corrections needed in the final line of your SQL statement.
_____________________________
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: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Querying Comma Separated Values - 6/1/2004 23:13:07
You also may need to put DateStart and DateEnd in brackets, i.e., [DateStart], [DateEnd]
_____________________________
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: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Querying Comma Separated Values - 6/2/2004 9:13:27
Here are some other things I have found. You have a text box in your form where the user specifies an AREA not if the one they need is not shown in the dropdown. However, you named the text box and the dropdown AREA. Having two form fields with the same name will cause a problem. Same holds true for DateStart and DateEnd. You have two fields each with that name. Change the names of the disabled text fields to DateStart1 and DateEnd1.
The field stampbooking is not in your form which would also trigger an error. Assuming you are using it to record the date and time the reservation is made, change the last line of your SQL to:
mySQL = mySQL & "#" & Now() & "#)"
By doing that, you won't need to have a form field.
Finally, it appears that the field BOOKINGNUMBER is probably the autonumber, primary key in your db. If so, it should not be included in the SQL statement. So you would need to replace these two lines:
mySQL = mySQL & "VALUES(" & Request.Form("BOOKINGNUMBER") & ","
mySQL = mySQL & "'" & Replace(Request.Form("CAMP"),"'","''") & "',"
with:
mySQL = mySQL & "VALUES('" & Replace(Request.Form("CAMP"),"'","''") & "',"
And change this line:
mySQL = mySQL & "(BOOKINGNUMBER, CAMP, DateStart, DateEnd, SPECIFICS, ADDRESS, CITY, PROVINCE, POSTALCODE, REGION, CAMPGROUP, [SECTION], YOUTH, ADULTS, EMAIL, OTHER, AREA, FIRSTNAME, LASTNAME, PHONE, [PASSWORD], CHECKINTIME, CHECKOUTTIME, SCOUTING, NONSCOUTING, CampSite, stampbooking)"
to:
mySQL = mySQL & "(CAMP, DateStart, DateEnd, SPECIFICS, ADDRESS, CITY, PROVINCE, POSTALCODE, REGION, CAMPGROUP, [SECTION], YOUTH, ADULTS, EMAIL, OTHER, AREA, FIRSTNAME, LASTNAME, PHONE, [PASSWORD], CHECKINTIME, CHECKOUTTIME, SCOUTING, NONSCOUTING, CampSite, stampbooking)"
I created a db table using the same fields you have used. Then made the above changes to your form and SQL and was finally able to successfully insert records.
< Message edited by betheball -- 6/2/2004 8:02:03 >
_____________________________
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: 386
Joined: 4/4/2004
Status: offline
|
RE: Querying Comma Separated Values - 6/2/2004 10:22:42
Thanks Duane
I'm working on it...
Question - if the field stampbooking is in the Access database.. do I need to still refer to it in the sql?
(Bookingnumber isn't the AutoNumber so I left to include that)
On the page that sends to the insert page the hidden fields are as follows
<input type="hidden" name="DateStart" value="<%=varDateStart%>">
<input type="hidden" name="DateEnd" value="<%=varDateEnd%>">
<input type="hidden" name="CAMP" value="Camp Wetaskiwin">
Are these correct?...
Can't get it to work with the following..
<% DIM conntemp, mySQL, myDSN
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("../fpdb/index.mdb")
'myDSN would be specific to your environment
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
FOR i = 1 to 26
IF Request("CampSite" & i) <> "" THEN
mySQL = "INSERT INTO WETASKIWIN1"
mySQL = mySQL & "(BOOKINGNUMBER, CAMP, DateStart, DateEnd, SPECIFICS, ADDRESS, CITY, PROVINCE, POSTALCODE, REGION, CAMPGROUP, [SECTION], YOUTH, ADULTS, EMAIL, OTHER, AREA, FIRSTNAME, LASTNAME, PHONE, [PASSWORD], CHECKINTIME, CHECKOUTTIME, SCOUTING, NONSCOUTING, CampSite)"
mySQL = mySQL & "VALUES(" & Request.Form("BOOKINGNUMBER") & ","
mySQL = mySQL & "'" & Replace(Request.Form("CAMP"),"'","''") & "',"
mySQL = mySQL & "#" & Request.Form("DateStart") & "#,"
mySQL = mySQL & "#" & Request.Form("DateEnd") & "#,"
mySQL = mySQL & "'" & Replace(Request.Form("SPECIFICS"),"'","''") & "',"
mySQL = mySQL & "'" & Replace(Request.Form("ADDRESS"),"'","''") & "',"
mySQL = mySQL & "'" & Replace(Request.Form("CITY"),"'","''") & "',"
mySQL = mySQL & "'" & Replace(Request.Form("PROVINCE"),"'","''") & "',"
mySQL = mySQL & "'" & Replace(Request.Form("POSTALCODE"),"'","''") & "',"
mySQL = mySQL & "'" & Replace(Request.Form("REGION"),"'","''") & "',"
mySQL = mySQL & "'" & Replace(Request.Form("CAMPGROUP"),"'","''") & "',"
mySQL = mySQL & "'" & Replace(Request.Form("SECTION"),"'","''") & "',"
mySQL = mySQL & "" & Request.Form("YOUTH") & ","
mySQL = mySQL & "" & Request.Form("ADULTS") & ","
mySQL = mySQL & "'" & Replace(Request.Form("EMAIL"),"'","''") & "',"
mySQL = mySQL & "'" & Replace(Request.Form("OTHER"),"'","''") & "',"
mySQL = mySQL & "'" & Replace(Request.Form("AREA"),"'","''") & "',"
mySQL = mySQL & "'" & Replace(Request.Form("FIRSTNAME"),"'","''") & "',"
mySQL = mySQL & "'" & Replace(Request.Form("LASTNAME"),"'","''") & "',"
mySQL = mySQL & "'" & Replace(Request.Form("PHONE"),"'","''") & "',"
mySQL = mySQL & "'" & Replace(Request.Form("PASSWORD"),"'","''") & "',"
mySQL = mySQL & "'" & Replace(Request.Form("CHECKINTIME"),"'","''") & "',"
mySQL = mySQL & "'" & Replace(Request.Form("CHECKOUTTIME"),"'","''") & "',"
mySQL = mySQL & "'" & Replace(Request.Form("SCOUTING"),"'","''") & "',"
mySQL = mySQL & "'" & Replace(Request.Form("NONSCOUTING"),"'","''") & "',"
mySQL = mySQL & "'" & Replace(Request.Form("CampSite" & i),"'","''") & "',"
mySQL = mySQL & "#" & Now() & "#)"
conntemp.execute (mySQL)
END IF
NEXT
conntemp.close
set conntemp=nothing
Response.write "Reservation added."
%>
< Message edited by rikki -- 6/2/2004 11:37:28 >
| |
|
|
|
rikki
Posts: 386
Joined: 4/4/2004
Status: offline
|
RE: Querying Comma Separated Values - 6/2/2004 12:45:25
Duane
I don't know if this is a clue to what's going on or not... but I enter a date of aug 6/05 and go through and I get this error
Microsoft JET Database Engine error '80040e14'
Syntax error in INSERT INTO statement.
/campbookings1/1WETASKIWIN/4InsertQuery.asp, line 43
If I enter a date of August 6, 2005
I get this error
Microsoft JET Database Engine error '80040e14'
Syntax error in date in query expression '#august 6'.
/campbookings1/1WETASKIWIN/4InsertQuery.asp, line 43
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Querying Comma Separated Values - 6/2/2004 12:50:57
You'd better post the most recent code for the INSERT. Also, what happens if you enter the date as 8/6/05? Did you include a form field for BOOKINGNUMBER?
_____________________________
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: 386
Joined: 4/4/2004
Status: offline
|
RE: Querying Comma Separated Values - 6/2/2004 12:59:32
Guess what...
It worked...
I kept playing around and making some changes...
I get the page to say
Reservation added...
Thank you...
I'm sorry to keep bugging you but if I wanted to show a custom confirmation page how do I do that?...
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Querying Comma Separated Values - 6/2/2004 13:06:27
quote:
ORIGINAL: rikki
Guess what...
It worked...
I kept playing around and making some changes...
I get the page to say
Reservation added...
Thank you...
I'm sorry to keep bugging you but if I wanted to show a custom confirmation page how do I do that?...
Before we both breathe a big sigh of relief, enter a reservation that includes 6 or 7 different campsites. Then open the db and make sure a db record exists for each campsite. All six or seven records should be essentially identical except the campsite value will differ.
Let me think on the confirmation issue for a minute. I am thinking you can simply use Request.Form values on the same page that performs the INSERT and let that page also serve as the confirmation 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: 386
Joined: 4/4/2004
Status: offline
|
RE: Querying Comma Separated Values - 6/2/2004 13:16:22
Yep, it worked!
Each shows as an individual record, with all the data the same except the CampSite info
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Querying Comma Separated Values - 6/2/2004 22:02:18
For the confirmation, I would do this:
Add this as the very first line on the page that contains the INSERT statement:
Response.Buffer="True"
Then, change this:
Response.write "Reservation added."
to:
Response.redirect("confirmation.asp?DateStart="&Request.Form("DateStart")&"&DateEnd="&Request.Form("DateEnd")&"&EMail="&Request.Form("EMAIL")&""
You then need to create confirmation.asp. On that page, I would create 2 db results regions. The first will display the sites reserved. To do that use a custom query of SELECT CampSite FROM WETASKIWIN1 WHERE DateStart=#::DateStart::# AND DateEnd=#::DateEnd::# AND EMAIL='::EMAIL::'
So you in creating your confirmation page, you may want to begin with something like:
Your reservation for the following sites has been confirmed:
Then the DRW showing the sites.
Then something like:
Your registration details are as follows:
Second DRW using this SQL:
SELECT TOP 1 * FROM WETASKIWIN1 WHERE EMAIL='::EMAIL::'
Something like that should do the trick.
_____________________________
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: 386
Joined: 4/4/2004
Status: offline
|
RE: Querying Comma Separated Values - 6/3/2004 21:26:55
Hi Duane
Thank you so much ! It's working great and I can finally see the light at the end of the tunnel.
Question: on the confirmation page I wanted to add a field to the first part.
"CAMP"
I changed the sql to:
fp_sQry="SELECT CAMP,CampSite FROM WETASKIWIN1 WHERE DateStart = #::DateStart::# AND DateEnd = #::DateEnd::# AND EMAIL = '::EMAIL::' AND CAMP = '::CAMP::'"
Now I get this--
Camp Wetaskiwin [Lodge] Camp Wetaskiwin [Lodge] Camp Wetaskiwin [Bunkhouse] Camp Wetaskiwin [Potlatch]
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Querying Comma Separated Values - 6/3/2004 21:45:07
Isn't the value for CAMP always the same? If so, I am not sure why you wouldn't just code that into the html rather than pull a value from the db that you always know what it is. Am I mistaken, could the value for CAMP be different than "Camp Wetaskiwin"?
_____________________________
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: 386
Joined: 4/4/2004
Status: offline
|
RE: Querying Comma Separated Values - 6/4/2004 14:27:28
I was just having a blonde moment!
Question... I have got the query to work not including the insert date function.
I did remove it and it works fine... But now I'm thinking I want to add it.
the Field in the database is "DateStamp"
I changed the sql to this..
mySQL = mySQL & "(BOOKINGNUMBER, CAMP, DateStart, DateEnd, SPECIFICS, ADDRESS, CITY, PROVINCE, POSTALCODE, REGION, CAMPGROUP, [SECTION], YOUTH, ADULTS, EMAIL, OTHER, AREA, FIRSTNAME, LASTNAME, PHONE, [PASSWORD], CHECKINTIME, CHECKOUTTIME, SCOUTING, NONSCOUTING, DateStamp, CampSite)"
And the last line in the code to this...
Do I put the code in as suggested previously?..
mySQL = mySQL & "#" & Now() & "#)"
I'm getting an error that says
Microsoft JET Database Engine error '80040e07'
Syntax error in date in query expression '##'.
/campbookings1/1WETASKIWIN/4InsertQuery.asp, line 46
< Message edited by rikki -- 6/4/2004 14:30:19 >
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Querying Comma Separated Values - 6/4/2004 19:20:08
That can't be your last line in the SQL if datestamp is not the last field you are inserting to.
mySQL = mySQL & "(BOOKINGNUMBER, CAMP, DateStart, DateEnd, SPECIFICS, ADDRESS, CITY, PROVINCE, POSTALCODE, REGION, CAMPGROUP, [SECTION], YOUTH, ADULTS, EMAIL, OTHER, AREA, FIRSTNAME, LASTNAME, PHONE, [PASSWORD], CHECKINTIME, CHECKOUTTIME, SCOUTING, NONSCOUTING, DateStamp, CampSite)"
They have to match up.
Off the subject, did you see my most recent post here:
http://www.frontpagewebmaster.com/m-200268/tm.htm
_____________________________
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: 386
Joined: 4/4/2004
Status: offline
|
RE: Querying Comma Separated Values - 6/5/2004 17:36:21
Hi
I got the other post... worked great!
I've fixed this other problem.
Thanks
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Querying Comma Separated Values - 6/5/2004 17:42:50
I truly am happy for you. You have shown tremendous perserverance. I was glad to help especially since the site dealt with a scout camp. I am leader of a patrol of 11-year old scouts and my father has been a scout leader for more than 35 years.
_____________________________
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.
| |
|
 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 
