|
| |
|
|
jaberwocky
Posts: 185
Joined: 8/8/2003
Status: offline
 |
RE: help with a form - 6/23/2004 18:12:05
thanks Spooky, can you help me with one more thing with this. If you look at my thumbnail here, what i am trying to do is now sent the Date value, the name value and the Shifts value all into an Access table after the user has entered the nec info. Is there anyway i can do all this in 1 sql line.. i guess make it loop for each entry grabbing the date, name, shifts number. I am trying the code below, but it gives me the following error:
][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same.
sValue1 = Request.Form("weekstarting")
sValue2 = Request.Form("fullname")
sValue3 = Request.Form("shiftnumber")
%>
<% sql1 = "Insert Into ShiftsWorkedCOPY (weekof, fullname, shiftsworked) values (#" & sValue1 & "#,'" & sValue2 & "'," & sValue3 & ");"
response.write sql1
conn.Execute sql1
%>
Thumbnail Image
 Attachment (1)
|
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: help with a form - 6/24/2004 8:41:11
Here is an example of doing multiple updates. The form is created by the DRW, but you should be able to do the same thing in ASP.
_____________________________
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.
| |
|
|
|
jaberwocky
Posts: 185
Joined: 8/8/2003
Status: offline
|
RE: help with a form - 6/24/2004 14:05:35
Did you post an example betheball? i dont see it. And Spooky I dont think i have explained myself properly. Once a week, the user will open this form, enter a Date at the top, and then beside each users name, enter the # of Shifts for that person. I want each of these entries (date,name,shifts) to go into my table as a new record in my db. So, record#1=date/name/shift; record #2= date/name/shifts..on up to record #125. My table is structured with 3 fields, 1 for Date, 1 for Name and 1 for #of Shifts. It isnt practical to have an Update button as i am not updating existing records but adding new ones. In my form i just dont want to have to have the user enter the date for each name, cause it is the same. Enter it once at the top, but then have that info go into each new record in my table.
Table in Access
Date Name Shifts
06/06/04 John 6
06/06/04 Sarah 3
06/06/04 Bill 2
06/13/04 John 4
06/13/04 Sarah 4
06/13/04 Bill 5
but instead of there only being 3 entries each week, there are 125 names to be entered.
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: help with a form - 6/24/2004 15:01:10
Sorry, do that with emails all the time. See if this thread helps any.
http://www.frontpagewebmaster.com/m-207665/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.
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: help with a form - 6/24/2004 15:32:36
Actually that is quite similar to what I posted. You need a distinct form field name for the name and shift number but no the date. I have a form that does exactly the same thing. The date is the same for each record but the other data changes. Here is the code that processes the form:
<% DIM conntemp, mySQL, myDSN
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("\pps\Team304\fpdb\Review304.mdb")
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
FOR i = 1 to 15
IF Request("AspectNumber" & i) <> "" THEN
mySQL = "INSERT INTO AHT (ADate, Application, Aspect, Calls, Talk, Hold, Wrap) VALUES"
mySQL = mySQL & " (#" & Request("ADate") & "#, " & Request("Application") & ", " & Request("AspectNumber" & i) & ", " & Request("Calls" & i) & ", " & Request("Talk" & i) & ", " & Request("Hold" & i) & ", " & Request("Wrap" & i) & ")"
conntemp.execute(mySQL)
END IF
NEXT
conntemp.close
set conntemp=nothing
Response.write "Record added."
%>
Notice how in the SQL, the field ADate and Application are not followed by "& i". That is because there is only one form field for ADate and Application. However, there are multiple form fields for AspectNumber, Calls, etc. Hence, the additions of "&i". The code loops through the form fields beginning with AspectNumber1 and inserts a record for each AspectNumber on the form. The key to making the whole thing work is having distinct form field names for each repeating item. For example, you are going to have 125 names so you want the form fields to be Name1, Name2, Name3, etc. That will allow the code to loop through and insert a new record for each Name field.
Does that make any more sense?
_____________________________
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.
| |
|
|
|
jaberwocky
Posts: 185
Joined: 8/8/2003
Status: offline
|
RE: help with a form - 6/24/2004 17:25:22
thanks Duane, but i think i am going to abandon this attempt and just let the user continue to use a form i created within Access itself.
The prob is with the part you say is key - to have have 125 separate form fields each with their own unique name..thats just a hassle. The way i have my form now it automat. picks up names from a query in my database and, much like you would do in a report to show data, it shows them on my form page, just in input text boxes.
<td><input type="text" name="fullname" size="25" disabled value="<%Response.Write(rs.Fields("FullName").Value)%>"></td>
If i have to actually have on my formpage a form field for each name i have, then that gets cumbersome (and big) as the number changes.. ie it is 125 now, but may change to 122, then 130 then 128, as people come and go.
It is no biggie, i will just let them continue to use the Access Form.
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: help with a form - 6/24/2004 18:19:10
quote:
If i have to actually have on my formpage a form field for each name i have, then that gets cumbersome (and big) as the number changes.. ie it is 125 now, but may change to 122, then 130 then 128, as people come and go.
Obviously, the choice is yours. However, you can append a number to the name of a form field quite easily and dynamically.
Using the code in your initial post, try something like this:
<html>
<body>
<form METHOD="POST" ACTION="shifts.asp">
<span id="dataPanel"
style="font:10pt;height:420px;width=800px;visibility:visible;overflow:scroll;background:#ffffff;border:0
inset black">
<table WIDTH="263" BORDER="0" CELLSPACING="0">
<%
i=0
On Error Resume Next
rs.MoveFirst
Do While Not RS.EOF
i=i+1
%>
<tr>
<td WIDTH="120"><input type="text" name="fullname<%=i%>" size="20" <%Response.Write(rs.Fields("fullname").Value)%>></font></td>
<td width="58"><input TYPE="text" NAME="shiftnumber<%=i%>" size="5">
</tr>
<%
rs.MoveNext
loop
recCount=i
%></span>
<td><input type="submit" value="Submit" name="submit"></left></td>
<input type = "HIDDEN" Name = "recCount" Value = <%=recCount%>>
</table>
</form>
</body>
</html>
If that works, you should then be able to preview your form in your browser and see the form fields named, fullname1, fullname2, shiftnumber1, shiftnumber2, etc. With that, you should then be able to use code like I provided to insert the records. It's really quite slick. I am more than willing to work through it with you.
_____________________________
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.
| |
|
|
|
jaberwocky
Posts: 185
Joined: 8/8/2003
Status: offline
|
RE: help with a form - 6/24/2004 18:50:18
thanks. i appreciate all the assistance, i find it frustrating tho when i dont always understand code n stuff.. but i am learning.
So i entered in my formpage the parts u had highlighted, and looked at my form page in the browser and it looks the same. so i guess this is good. I tried to adapt the other code you gave me, but the records dont seem to be making it into the db table.
I changed some of the names so that my form boxes, table fields etc are all consistent too.
<% DIM conntemp, mySQL, myDSN
myDSN = "DSN=attendance2004"
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
FOR i = 1 to 96
IF Request("Fullname" & i) <> "" THEN
mySQL = "INSERT INTO ShiftsWorkedCOPY (WeekOf, FullName, ShiftsWorked) VALUES"
mySQL = mySQL & " (#" & Request("WeekOf") & "#, '" & Request("FullName" & i) & "', " & Request("ShiftsWorked" & i) & ")"
conntemp.execute(mySQL)
END IF
NEXT
conntemp.close
set conntemp=nothing
Response.write "Record added."
%>
There are actually 96 Names not 125. If this number changes do i then have to change the line above "FOR i = 1 to 96" ??
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: help with a form - 6/24/2004 19:05:12
If you open your form and get no error that is a good start. When you open the form in the browser and do view source are the numbers being added to the end of the form name? Meaning, do you see fullname1, fullname2, etc.? That is essential.
Also, since you said the number of records will change, that is why I created the hidden field called recCount which will count the actual number of records. So change the code that performs the insert to:
<% DIM conntemp, mySQL, myDSN, recCount
recCount=Request.Form("recCount")
myDSN = "DSN=attendance2004"
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
FOR i = 1 to recCount
IF Request("Fullname" & i) <> "" THEN
mySQL = "INSERT INTO ShiftsWorkedCOPY (WeekOf, FullName, ShiftsWorked) VALUES"
mySQL = mySQL & " (#" & Request("WeekOf") & "#, '" & Request("FullName" & i) & "', " & Request("ShiftsWorked" & i) & ")"
conntemp.execute(mySQL)
END IF
NEXT
conntemp.close
set conntemp=nothing
Response.write "Record added."
%>
I know what you mean about the coding. When I first started here, Spooky and rdouglass answered a lot of my posts and often I didn't understand a single line of the code they provided. I have found that it comes little by little. One day you look at a piece of code and a light goes on. That is the most rewarding part.
_____________________________
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.
| |
|
|
|
jaberwocky
Posts: 185
Joined: 8/8/2003
Status: offline
|
RE: help with a form - 6/24/2004 19:24:36
ahh now i see what your talking about... yes if i look at the Source i get Fullname1 on upto Fullname96 and Shiftsworked1 on up to Shiftsworked96.
I added all the extra recCount code to my shift2.asp page as well, but now i am getting
Type mismatch: '[string: ""]' shifts2.asp, line 8
which is the line FOR i = 1 to recCount
I had a counterpart of mine in a satillite office of where i work that new asp inside/out. i used to rely on him and get help and code examples, but alas he is gone now. I live for this forum. Mostly i just try to take existing asp and adapt it for whatever i am working on now.
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: help with a form - 6/24/2004 19:35:06
So when you do view source, does this line show "96"
<input type = "HIDDEN" Name = "recCount" Value = 96>
_____________________________
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.
| |
|
|
|
jaberwocky
Posts: 185
Joined: 8/8/2003
Status: offline
|
RE: help with a form - 6/24/2004 19:40:42
nope. in the Source it just shows
<input type = "HIDDEN" Name = "recCount" Value = >
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: help with a form - 6/24/2004 20:22:25
OK, now we are testing my asp skills. See what happens when you reverse these two lines on the page with your form:
Instead of
loop
recCount=i
Try:
recCount=i
loop
If that fails, post the full code for the page with your form for a look.
< Message edited by betheball -- 6/24/2004 23:35:33 >
_____________________________
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.
| |
|
|
|
jaberwocky
Posts: 185
Joined: 8/8/2003
Status: offline
|
RE: help with a form - 6/25/2004 11:49:06
LOL..hmmm i dont even seem to have that line in my formcode. Where should it go? This is what i have in my formpage (minus alot of the html stuff):
<%
dim conn
Session.timeout = 2
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "attendance2004","",""
sQl="SELECT * FROM ""servicesStaffQuery"" ORDER BY ""status"" ASC, ""fullname"" ASC"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 3, 3
%>
<form METHOD="POST" ACTION="shifts2.asp">
<p>Week Starting: <input type="text" name="weekof" size="20"> </p>
<table WIDTH="220" BORDER="0" CELLSPACING="0">
<tr>
<th WIDTH="140"><span style="font-weight: 400">Name</font></span></th>
<th WIDTH="100"><span style="font-weight: 400># Shifts Sched</span></th>
</tr>
</table>
<span id="dataPanel"
style="font:10pt;height:420px;width=400px;visibility:visible;overflow:scroll;background:#ffffff;border:0
inset black">
<table WIDTH="220" BORDER="0" CELLSPACING="0">
<font FACE="Arial" COLOR="#000000">
<%
i=0
On Error Resume Next
rs.MoveFirst
Do While Not RS.EOF
i=i+1
%>
<tr>
<td WIDTH="120"><input type="text" name="fullname<%=i%>" size="25" disabled value="<%Response.Write(rs.Fields("FullName").Value)%>"></font></td>
<td width="58"><input TYPE="text" NAME="shiftsworked<%=i%>" size="5">
</tr>
<%
rs.MoveNext
loop%></span>
<td><input type="submit" value="Submit" name="submit"></left></td>
<input type = "HIDDEN" Name = "recCount" Value = <%=recCount%>>
</table>
</form>
</body>
</html>
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: help with a form - 6/25/2004 12:12:57
quote:
LOL..hmmm i dont even seem to have that line in my formcode
As I got thinking about it, I suspected maybe you had omitted that line. Anyway, lets make it simpler. Try just changing this:
<input type = "HIDDEN" Name = "recCount" Value = <%=recCount%>>
to
<input type = "HIDDEN" Name = "recCount" Value = <%=i%>>
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.
| |
|
|
|
jaberwocky
Posts: 185
Joined: 8/8/2003
Status: offline
|
RE: help with a form - 6/25/2004 13:29:25
OK, now i get this showing in my ViewSource:
<input type = "HIDDEN" Name = "recCount" Value = 96>
When i test my form though, by entering values for 5 of the names, the data doesnt seem to be going into my table. Is this a problem in my shifts2.asp page (below)
<% DIM conntemp, mySQL, myDSN, recCount
recCount=Request.Form("recCount")
myDSN = "DSN=attendance2004"
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
FOR i = 1 to recCount
IF Request("Fullname" & i) <> "" THEN
mySQL = "INSERT INTO ShiftsWorkedCOPY (WeekOf, FullName, ShiftsWorked) VALUES"
mySQL = mySQL & " (#" & Request("WeekOf") & "#, '" & Request("Fullname" & i) & "', " & Request("ShiftsWorked" & i) & ")"
conntemp.execute(mySQL)
END IF
NEXT
conntemp.close
set conntemp=nothing
Response.write "Record added."
%>
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: help with a form - 6/25/2004 13:40:38
Any error message?
_____________________________
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.
| |
|
|
|
jaberwocky
Posts: 185
Joined: 8/8/2003
Status: offline
|
RE: help with a form - 6/25/2004 14:56:39
sorry for the delay .. it's hell friday LOL
umm no, no error message, after i click Submit, it just jumps to the message "Records added", from the response.write on my shift2.asp page. When i check the db though, the records are not in my table
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: help with a form - 6/25/2004 15:05:33
Strange. And you are checking the table named: ShiftsWorkedCOPY
_____________________________
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.
| |
|
|
|
jaberwocky
Posts: 185
Joined: 8/8/2003
Status: offline
|
RE: help with a form - 6/25/2004 15:18:25
i know..strange.. and it's bugging me!
I am opening the proper DSN name "attendance2004"
i am referencing the proper .asp page from my formpage - shifts2.asp
my sql line contains the proper table name: ShiftsWorkedCOPY and contains the proper field names: WeekOf, Fullname and ShiftsWorked
maybe there is something wrong with how my database conn is opening?
| |
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: help with a form - 6/25/2004 17:46:49
I think I have it sorted.
1 - A disabled field will not submit to the database. So, change the form field from "disabled" to "readonly". Or perhaps better, put the fullname in a hidden form field and display the names as text not a form field.
2 - In the code that performs the update, change:
IF Request("Fullname" & i) <> "" THEN
to
IF Request("WeekOf" & i) <> "" THEN
Otherwise, you would have to complete every "shifts worked" form field to avoid an error.
I think with those two changes you will be in business.
_____________________________
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.
| |
|
|
|
jaberwocky
Posts: 185
Joined: 8/8/2003
Status: offline
|
RE: help with a form - 6/25/2004 18:47:05
argghh why do i do this? I buggered it up somehow. I changed the 'disabled value.." to 'readonly' and it actually worked fine. Then i went in to try to do the hidden field thing that you recommended for 'fullname' and just show it as text and i am sure i buggered up the form, cause now when i do the entries i dont get the info going into the database again. I tried to revert back to the original, but still wont go into the db. Did i bugger up the code in these lines? I cant see ...
<td WIDTH="120"><input type="text" name="fullname<%=i%>" size="25" readonly value="<%Response.Write(rs.Fields("FullName").Value)%>"></font></td>
<td width="58" align="center"><font color="blue"><input TYPE="text" NAME="shiftsworked<%=i%>" size="5">
I didnt touch my asp page that actually does the updating to the db
| |
|
 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¥ 
i am sorry you had to spend so much time on it :( 