RE: Querying Comma Separated Values

RE: Querying Comma Separated Values (Full Version)

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

Message

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

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

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

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

BeTheBall -> RE: Querying Comma Separated Values (6/2/2004 11:36:35)
If bookingnumber is not the autonumber, you need a form field with that name. I do not see a form field titled "bookingnumber". If your db field named stampbooking has a default value in the db, yes, you can exclude it from the SQL. Your hidden fields are fine, but make sure you change the name of the disabled fields at the top of the page so they are not the same name. Even though it may not seem like it, you are getting close. I think [&:]

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

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

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

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

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

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

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

Page: << < prev 1 2 [3]

0.078125