|
| |
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
 |
Add multiple records with one form submission - 8/3/2003 12:35:45
I have a form which has a field for the date and then 8-10 fields where the user can enter a time of day for the chosen date. What I want to do is add a new record for each time that has a value. For example, the user completes the date field and then enters 12:00 in Time1, 1:00 in Time2 and 2:00 in Time3. Time4 through Time8 are left blank. Upon submit, I want to add a 3 new records to the db. The db has two fields, Day and TimeofDay. So the entry for Day will be the same for all three new records but TimeofDay will be different. Any ideas on what my SQL would look like or if this is even possible?
|
|
|
|
rdouglass
Posts: 9270
From: Biddeford, ME USA
Status: offline
|
RE: Add multiple records with one form submission - 8/4/2003 10:21:43
quote:
...if this is even possible?
Doesn' t sound impossible to me... Unfortunately, (or maybe fortunately depending on your point of view), I don' t think you' ll be able to use the DRW - at least not easily.
If it were me, I' d loop thru the time fields first just to get a count of how many items to add. Something like:
<% DIM myItemCount
myItemCount = 0
For i = 1 to 8
IF Request(" Time" & i) > " " THEN
myItemCount = myItemCount + 1
END IF
%>
Then, I' d build a loop something like this:
<%
DIM conntemp, mySQL
set conntemp=server.createobject(" adodb.connection" )
conntemp.open myDSN
' myDSN would be specific to your environment
FOR i = 1 to myItemCount
mySQL = " INSERT INTO myTable ([Day], TimeofDay) VALUES (#" & Request(" myDayField" ) & " #, #" & Request(" Time" & myItemCount) & " #)"
conntemp.execute(mySQL)
NEXT
conntemp.close
set conntemp=nothing
%>
See, what I' m suggesting is check each Time for an entry and count ' em up. Then loop thru each one, adding it to the DB as we go. Does that make sense?
Of course, this script doesn' t check for errors (like if they skip a time space) nor has been tested, but I think this is one way. Hope it helps...
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Add multiple records with one form submission - 8/4/2003 12:42:25
rdouglass??? I thought you had died. I am so glad to see you back. I won' t have a chance to try your idea for a few days, but am sure I will have additional questions since I have only done one pure ASP page. Until then, welcome back. It is so good to see your name on the board again. I am sure Spooky will feel relieved that someone else can answer some of the more complicated questions.
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Add multiple records with one form submission - 8/5/2003 15:16:38
OK, as promised, I am back. I have created a form in FP through which I want to send anywhere from 1-4 records to a DB called Appointment. The form consists of 5 text boxes, Appt_Day, Appt_Time1, Appt_Time2, Appt_Time3 and Appt_Time4. The DB has only two fields, Appt_Day and Appt_Time. I need to learn how to combine my form and the code given by rdouglas. Can I put the code on the same page as the form? If so, how do I tie it to the submit button on the form? Or, should I have the form submit to another page and put the rdouglas' s code on the other page? Also, I prefer to use a dsn-less connection, but if I understand correctly the code is set up for DSN? As you can see, I have a long way to go before I understand this very well, but appreciate any help I can get. The more basic, the better  .
| |
|
|
|
rdouglass
Posts: 9270
From: Biddeford, ME USA
Status: offline
|
RE: Add multiple records with one form submission - 8/6/2003 9:48:42
All the code I posted would go on the same page that the form posts to. Normally I use separate pages, but is not necessary.
See, my theory is this:
The first code section scans thru the submitted form data fields Time1 thru Time8 to see if there is an entry. (If you' re only using 4 times, change the 8' s to 4' s.) If there is something in the field, add one to myItemCount.
Then I open the DB connection and loop thru each item that has something (using myItemCount) and build a SQL statement for each and execute it. Do it for ' myItemCount' times.
Does that help at all?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Add multiple records with one form submission - 8/6/2003 12:20:25
I am giving this a shot. I posted your code on a new page, PostAppt.asp. Here is the code:
<% DIM myItemCount
myItemCount = 0
For i = 1 to 4
IF Request(" Appt_Time" & i) > " " THEN
myItemCount = myItemCount + 1
END IF
%>
<%
DIM conntemp, mySQL
set conntemp=server.createobject(" adodb.connection" )
conntemp.open Appointment
' myDSN would be specific to your environment
FOR i = 1 to myItemCount
mySQL = " INSERT INTO Appointment (Appt_Day, App_Time) VALUES (#" & Request(" Appt_Day" ) & " #, #" & Request(" Appt_Time" & myItemCount) & " #)"
conntemp.execute(mySQL)
NEXT
conntemp.close
set conntemp=nothing
%>
I get this error:
Microsoft VBScript compilation error ' 800a0410'
Invalid ' for' loop control variable
/pps/PostAppt.asp, line 19
FOR i = 1 to myItemCount
Also, using this code, how do I name the four text boxes that are going to feed into the Time field? Do I name them all the same?
| |
|
|
|
rdouglass
Posts: 9270
From: Biddeford, ME USA
Status: offline
|
RE: Add multiple records with one form submission - 8/6/2003 12:50:09
quote:
how do I name the four text boxes that are going to feed into the Time field? Do I name them all the same?
I based the code on your post about naming the fields:
quote:
The form consists of 5 text boxes, Appt_Day, Appt_Time1, Appt_Time2, Appt_Time3 and Appt_Time4
so I looped thru Appt_Time 1 thru 4. I also had a small but significant error in my code  . This is what I just came up with:
<% DIM myItemCount, conntemp, mySQL, myDSN
myItemCount = 0
For i = 1 to 4
IF Request(" Appt_Time" & i) > " " THEN
myItemCount = myItemCount + 1
END IF
NEXT
Response.write(myItemCount)
myDSN = " DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath(" ../fpdb/db.mdb" )
' myDSN would be specific to your environment
set conntemp=server.createobject(" adodb.connection" )
conntemp.open myDSN
FOR i = 1 to myItemCount
mySQL = " INSERT INTO Appointment (Appt_Day, App_Time) VALUES"
mySQL = mySQL & " (#" & Request(" Appt_Day" ) & " #, #" & Request(" Appt_Time" & myItemCount) & " #)"
Response.write(mySQL)
conntemp.execute(mySQL)
NEXT
conntemp.close
set conntemp=nothing
%>
I added a coupla' response.writes to help troubleshoot but remove ' em when (or should I say " if" ) we get it working. Don' t worry, this is very do-able. The only place I see any issue at all is your connection string and/or DB driver and I' m sure we can figure that out.
The only other potential issue I can see right off is if the user leaves a blank between Appt_Times. For instance, the way the code is now, if a user should enter times in Appt_Time1 and Appt_Time3, the code will count 2 items and try to pull values from Appt_Time1 and Appt_Time2. This can be accomplished with a few minor adjustments, but may not be at all necessary.
Besides, let' s keep it simple for now..
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Add multiple records with one form submission - 8/6/2003 13:47:59
You are correct. I ran the page and it correctly counted 4 records, but then errored out on the db connection. I need to use a dsn-less connection because I don' t have access to the server and our IT people would take weeks to set up a dsn. Here is the error I got:
Microsoft OLE DB Provider for ODBC Drivers error ' 80004005'
[Microsoft][ODBC Microsoft Access Driver] Could not find file ' (unknown)' .
/pps/PostAppt.asp, line 18
If it helps, the path to my db (which is in a subweb) is D:\inetpub\wwwroot\pps\fpdb\Appointment.mdb. I do believe our server supports the use of parent paths.
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Add multiple records with one form submission - 8/6/2003 14:34:17
Oh so close. It now works. The only problem is when the insert occurred, it inserted the value from the Appt_Time4 text box into all four records, instead of inserting the value of Appt_Time1 into record 1, Appt_Time2 into record 2, etc.
| |
|
|
|
rdouglass
Posts: 9270
From: Biddeford, ME USA
Status: offline
|
RE: Add multiple records with one form submission - 8/6/2003 15:05:07
I just looked again and we should be able to be a little more efficient and also deal with the issue of anyone skipping a line at the same time. Notice what I did? You' ll just have to substitute myDSN for the one that is working. By the way, which one did you use?
<% DIM conntemp, mySQL, myDSN
myDSN = " DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath(" ../fpdb/db.mdb" )
' myDSN would be specific to your environment
set conntemp=server.createobject(" adodb.connection" )
conntemp.open myDSN
FOR i = 1 to 4
IF Request(" Appt_Time" & i) > " " THEN
mySQL = " INSERT INTO Appointment (Appt_Day, App_Time) VALUES"
mySQL = mySQL & " (#" & Request(" Appt_Day" ) & " #, #" & Request(" Appt_Time" & i) & " #)"
Response.write(mySQL)
conntemp.execute(mySQL)
END IF
NEXT
conntemp.close
set conntemp=nothing
%>
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Add multiple records with one form submission - 8/6/2003 15:17:02
That did it! Did I mention how nice it is to have you back???
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Add multiple records with one form submission - 8/14/2003 12:27:16
One more question that may help me with a similar project. In the above code we dealt with adding 4 records with up to 4 differenct Appt_Time values. How would the code change if I had two fields with changing values? For example instead of Appt_Time, I had Beginning_Time and Ending_Time? What I am doing is inputting appointments but since appointments can be of varying durations, I need a beginning time and and ending time. I would like to enter the entire day's appointments with one form submission.
| |
|
|
|
rdouglass
Posts: 9270
From: Biddeford, ME USA
Status: offline
|
RE: Add multiple records with one form submission - 8/14/2003 15:38:30
I don't see why you couldn't use:
mySQL = mySQL & " (#" & Request(" Appt_Day" ) & " #, #" & Request("Start_Time" & i) & " #, #" & Request("End_Time" & i) & " #)"
Would something like that work???
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Add multiple records with one form submission - 8/14/2003 15:45:16
Thanks rdouglass, that does work. My problem stems from not knowing ASP very well so often when I look at code I am not sure what each piece does. In this case I found myself thinking I would have to define a variable for each set of text boxes, ie, one for begin time and one for end time. I really do appreciate the help and find with each question I am "getting it" a little better.
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Add multiple records with one form submission - 8/14/2003 15:59:05
quote:
Isn't that the ultimate goal?
Yes it is. I am just afraid that by the time I reach the goal, people will no longer remember what ASP even was!!!
Can you help with one last piece of this? How do I code in success or failure? In other words, after the user submits the form, can I include with the code that inserts the record some code that returns a message, say "Record added" if the insert was successful?
| |
|
 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¥ 
