|
| |
|
|
rikki
Posts: 386
Joined: 4/4/2004
Status: offline
 |
betheball... can you help? - 6/21/2004 15:45:10
Hi Duane
I'm asking you because you have helped me so much to get this database to where it is!
I'm trying to do an edit page and then update..
I've got the following code....
What happens is the bookings that are made that have the BOOKINGNUMBER=0 are new. The camp administrator needs to verify payment and then assign a BOOKINGNUMBER. So, I need them to be able to view all the bookings =0 and then edit to assign a booking number to all those that apply.
I'd like the update to cover all the sites booked, so they don't have to do one at a time. I can do something in DIW but it's not near what I'd like.
Can you help?
<html>
<head>
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>BOOKINGNUMBER</title>
</head>
<body>
<table border="1">
<thead>
<tr>
<td> </td>
</tr>
</thead>
<tbody>
<!--#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 (BOOKINGNUMBER = ::BOOKINGNUMBER::) ORDER BY DateStart ASC"
fp_sDefault="BOOKINGNUMBER=0"
fp_sNoRecords="<tr><td colspan=28 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=5
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=28
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<tr>
<td>
<p> </td>
<td>
<%=FP_FieldVal(fp_rs,"BOOKINGNUMBER")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"AutoNumber")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"CAMPGROUP")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"LASTNAME")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"FIRSTNAME")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"EMAIL")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"CAMP")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"DateStart")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"DateEnd")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"CampSite")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"ADDRESS")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"CITY")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"PROVINCE")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"POSTALCODE")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"REGION")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"SECTION")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"YOUTH")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"ADULTS")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"OTHER")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"AREA")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"PHONE")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"PASSWORD")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"CHECKINTIME")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"CHECKOUTTIME")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"SCOUTING")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"NONSCOUTING")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"DateStamp")%> </td>
</tr>
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>
<form method="POST" action="EDIT.asp?AutoNumber=<%=Request.form("AutoNumber")%>">
<p><input type="submit" value=" EDIT" name="B1"></p>
</form>
</body>
</html>
|
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: betheball... can you help? - 6/21/2004 16:34:21
Bulk updates are not particularly easy, but can be done. I would begin by taking this line of code:
<form method="POST" action="EDIT.asp?AutoNumber=<%=Request.form("AutoNumber")%>">
Change it to simply:
<form method="POST" action="EDIT.asp">
and move it near the top of your page, just before the opening table tag, <table>
Then, after this:
<% end if %>
add:
<% i=0 %>
And after:
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
add:
<% i=i+1 %>
Then, change:
<%=FP_FieldVal(fp_rs,"BOOKINGNUMBER")%>
To:
<Input Type = "Text" Name = "BookingNumber<%=i%>" Size = "20" Value = "<%=FP_FieldVal(fp_rs,"BOOKINGNUMBER")%>">
Then, just before <!--#include file="../_fpclass/fpdbrgn2.inc"-->
add:
<Input Type = "Hidden" Name = "UniqueIDField<%=i%>" Value = "<%=FP_FieldVal(fp_rs,"UniqueIDField")%>">
NOTE: Substitue the actual name of the autonumber field in your db in place of "UniqueIDField".
What that should do is create a form with all the records with a BOOKINGNUMBER of 0 and assign a unique name to the form fields. This should allow us to then use an UPDATE statement similar to the one we used to create the records. Let's first verify that the above code changes actually work. After you make the changes test the page in your browser. Then in Internet Explorer, click the "View" - "Source" option and make sure the BookingNumber and the UniqueID form fields are being named BOOKINGNUMBER1, BOOKINGNUMBER2, and UniqueID1, UniqueID2 etc. If we have gotten that done successfully, then we can create a page in pure ASP that should perform the update.
Good luck.
_____________________________
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: betheball... can you help? - 6/21/2004 17:05:12
Worked great!
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: betheball... can you help? - 6/21/2004 18:14:48
Great! Now add another hidden form field. Below this:
<Input Type = "Hidden" Name = "UniqueIDField<%=i%>" Value = "<%=FP_FieldVal(fp_rs,"UniqueIDField")%>">
add
<Input Type = "Hidden" Name = "recCount" Value = "<%=i%>">
Now, since the page you are submitting to is EDIT.asp, open EDIT.asp and switch to html view. Then, delete everything between the <body> and </body> tags. Then insert this code:
<% DIM conntemp, mySQL, myDSN, recCount
recCount=Request.Form("recCount")
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("\fpdb\YourDB.mdb")
'myDSN would be specific to your environment
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
FOR i = 1 to recCount
IF Request("BOOKINGNUMBER" & i) <> "" THEN
mySQL = "UPDATE WETASKIWIN1 SET BOOKINGNUMBER = "&Request("BOOKINGNUMBER" & i)&" WHERE UniqueIDField = &Request("UniqueIDField" & i)&"
conntemp.execute(mySQL)
END IF
NEXT
Response.write mySQL
Response.End
conntemp.close
set conntemp=nothing
Response.write "Update Complete!"
%>
Be sure to enter the correct db name and path in this line:
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("\fpdb\YourDB.mdb").
_____________________________
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: betheball... can you help? - 6/22/2004 9:49:31
Hi Duane
I think I've followed everything exactly ... but I'm getting the following error...
Microsoft VBScript compilation error '800a0401'
Expected end of statement
/campbookings1/1WETASKIWIN/EDIT.asp, line 25
mySQL = "UPDATE WETASKIWIN1 SET BOOKINGNUMBER = "&Request("BOOKINGNUMBER" & i)&" WHERE AutoNumber = &Request("AutoNumber" & i)&"
--------------------------------------------------------------------------------------------------------------^
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: betheball... can you help? - 6/22/2004 10:03:11
Add a second double quote at the end of the line:
mySQL = "UPDATE WETASKIWIN1 SET BOOKINGNUMBER = "&Request("BOOKINGNUMBER" & i)&" WHERE AutoNumber = &Request("AutoNumber" & i)&""
_____________________________
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: betheball... can you help? - 6/22/2004 21:28:20
Duane
Not sure if it's working the way it's suppose to..
When I did the editing as suggested I still had the update button on the form..
.....<input type = "Hidden" Name = "AutoNumber<%=i%>" Value = "<%=FP_FieldVal(fp_rs,"AutoNumber")%>">
<input type = "Hidden" Name = "recCount" Value = "<%=i%>">
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>
<input type="submit" value="UPDATE" name="B1"></p>
</form>....
Another question... I've finding you can go in and submit for a camp but have never selected a site.. what's the best way to avoid that?
< Message edited by rikki -- 6/22/2004 21:54:44 >
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: betheball... can you help? - 6/22/2004 23:17:13
So when you click the "Update" button, does the form submit to Edit.asp and are the records in fact being updated? If not, what is happening?
As for the other issue, you probably will need some javascript to ensure each one checkbox is marked. You might want to google on checkboxes and javascript. I'll do a bit of a search as well.
_____________________________
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: betheball... can you help? - 6/23/2004 9:22:28
Got the code for checking the checkboxes! If your interested I can post it...
When I click on submit I'm getting the following error...
Microsoft VBScript compilation error '800a0401'
Expected end of statement
/campbookings1/1WETASKIWIN/EDIT.asp, line 25
mySQL = "UPDATE WETASKIWIN1 SET BOOKINGNUMBER = "&Request("BOOKINGNUMBER" & i)&" WHERE AutoNumber = &Request("AutoNumber" & i)&""
--------------------------------------------------------------------------------------------------------------^
I then removed the BOOKINGNUMBER and the AutoNumber from the display and I now get a different error..
Microsoft VBScript runtime error '800a000d'
Type mismatch: '[string: "1, 2, 3, 4, 5, 6"]'
/campbookings1/1WETASKIWIN/EDIT.asp, line 21
Here's the edit code:
<html>
<head>
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>
<body>
<% DIM conntemp, mySQL, myDSN, recCount
recCount=Request.Form("recCount")
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 recCount
IF Request("BOOKINGNUMBER" & i) <> "" THEN
mySQL = "UPDATE WETASKIWIN1 SET BOOKINGNUMBER = "&Request("BOOKINGNUMBER" & i)&" WHERE AutoNumber = "&Request("AutoNumber" & i)&""
conntemp.execute(mySQL)
END IF
NEXT
Response.write mySQL
Response.End
conntemp.close
set conntemp=nothing
Response.write "Update Complete!"
%>
</body>
</html>
< Message edited by rikki -- 6/23/2004 9:44:44 >
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: betheball... can you help? - 6/23/2004 9:36:49
Stupid fingers!
mySQL = "UPDATE WETASKIWIN1 SET BOOKINGNUMBER = "&Request("BOOKINGNUMBER" & i)&" WHERE AutoNumber = "&Request("AutoNumber" & i)&""
_____________________________
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: betheball... can you help? - 6/23/2004 9:48:08
I made the change...
I get the following error now...
Microsoft VBScript runtime error '800a000d'
Type mismatch: '[string: "1, 2, 3, 4, 5, 6"]'
/campbookings1/1WETASKIWIN/EDIT.asp, line 21
I posted the current edit code above...
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: betheball... can you help? - 6/23/2004 10:12:55
Can you post the current code for the update form?
_____________________________
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: betheball... can you help? - 6/23/2004 10:48:03
OK, think I found the problem. In the page with your form, move this line:
<Input Type = "Hidden" Name = "recCount" Value = "<%=i%>">
to just after:
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
I think that should clear it up.
_____________________________
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: betheball... can you help? - 6/30/2004 20:21:13
Some code changes should make this process run more efficiently and more how you want. First, since you only want to update the BOOKINGNUMBER, it makes no sense to return all fields in your SQL which is what happens when you use SELECT *. Second, since all records with matching email and Start and End dates need to have the same BOOKINGNUMBER, it would make sense to only return one record per email/startdate/enddate and then let the SQL update all records with the matching email/startdate/enddate. So, let's make these changes. First to the form, try this.
Change the SQL to:
fp_sQry="SELECT DISTINCT Email, DateStart, DateEnd, BOOKINGNUMBER FROM WETASKIWIN1 WHERE (BOOKINGNUMBER = 0) ORDER BY DateStart ASC"
Then, change the code between <!--#include file="../_fpclass/fpdbrgn1.inc"--> and <!--#include file="../_fpclass/fpdbrgn2.inc"-->
to:
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<% i=i+1 %>
<td>
<%=FP_FieldVal(fp_rs,"EMAIL")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"DateStart")%> </td>
<td>
<%=FP_FieldVal(fp_rs,"DateEnd")%> </td>
<td>
<Input Type = "Text" Name = "BookingNumber<%=i%>" Size = "20" Value = "<%=FP_FieldVal(fp_rs,"BOOKINGNUMBER")%>">
</td>
</tr>
<Input Type = "Hidden" Name = "EMAIL<%=i%>" Value = "<%=FP_FieldVal(fp_rs,"EMAIL")%>">
<Input Type = "Hidden" Name = "DateStart<%=i%>" Value = "<%=FP_FieldVal(fp_rs,"DateStart")%>">
<Input Type = "Hidden" Name = "DateEnd<%=i%>" Value = "<%=FP_FieldVal(fp_rs,"DateEnd")%>">
<Input Type = "Hidden" Name = "recCount" Value = "<%=i%>">
<!--#include file="../_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>
That should give you one record in your form for each unique email/datestart/dateend when the bookingnumber equal zero, instead of all the record with a bookingnumber of zero.
Then change the SQL that performs the UPDATE to:
mySQL = "UPDATE WETASKIWIN1 SET BOOKINGNUMBER = "&Request("BOOKINGNUMBER" & i)&" WHERE EMAIL = '"&Request("EMAIL" & i)&"' AND DateStart = #"&Request("DateStart" & i)&"# AND #"&Request("DateEnd" & i)&"#"
Work thru that and see if it works a little more like you want.
_____________________________
_____________________________
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: betheball... can you help? - 6/30/2004 21:21:42
HI
It worked!
Thank you again!
< Message edited by rikki -- 6/30/2004 21:23:26 >
| |
|
 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 
