help with a form (Full Version)

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



Message

jaberwocky -> help with a form (6/23/2004 15:04:19)

I cant seem to get this form to work. I need to create a form that will list peoples names from a query in my Access DB. Beside each name there will be another input box where the user can enter the numner of shifts for each user listed. There will be a submit button which will send all this info to a new table in my db. The problem is i cant seem to get my list of names to appear in my form. It should be simple but it dont wanna work.. any hints? 
...
Set conn = Server.CreateObject("ADODB.Connection")
    conn.open "attendance2004","",""
    	sQl="SELECT * FROM ""servicesStaffQuery"" ORDER BY ""fullname"" ASC"
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open sql, conn, 3, 3
%>
<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">
 <%
On Error Resume Next
rs.MoveFirst
Do While Not RS.EOF
%>
<tr>
    <td WIDTH="120"><input type="text" name="fullname" size="20" <%Response.Write(rs.Fields("fullname").Value)%>></font></td>
    <td width="58"><input TYPE="text" NAME="shiftnumber" size="5">
  </tr>
  
<%
rs.MoveNext
loop%></span>
      <td><input type="submit" value="Submit" name="submit"></left></td>

</table>
</form>
</body>
</html>


Is there something wrong with my rs.MoveFirst line or my <%Response.Write(rs.Fields("fullname").Value)% ??
Below is a pic of what the form should look like and you can see where i want all the names to appear down the left

[image]local://upfiles/8498/A46D99F5F4E64FCFB8C4C464D94F17D0.jpg[/image]



Spooky -> RE: help with a form (6/23/2004 16:37:14)

<td WIDTH="120"><input type="text" name="fullname" size="20" value="<%Response.Write(rs.Fields("fullname").Value)%>" ></.......



jaberwocky -> 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		
	%>


[image]local://upfiles/8498/8FB7FEF4066448BBA177EB7E5DFC82FD.jpg[/image]



Spooky -> RE: help with a form (6/23/2004 22:05:38)

So, if you update 1 or many records, you wish to insert them into the database?

You wont do that with one SQL string, youll need to loop through the values updating the changed records.

This may mean you need to identify each row and form input uniquely.
One way would be : 

    <td WIDTH="120"><input type="text" name="fullname_<%=rs(0)%>" size="20" Value="<%Response.Write(rs.Fields("fullname").Value)%>"></font></td>
    <td width="58"><input TYPE="text" NAME="shiftnumber_<%=rs(0)%>" size="5">
<input TYPE="hidden" NAME="RecordID" value="<%=rs(0)%>">
  </tr>


This will mean each form input gets modified with the unique ID of the record
eg : fullname_123, shiftnumber_123

You would then use asp to locate the correct value for each record.

The easiest method would be one update button per record :-)



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



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



jaberwocky -> RE: help with a form (6/24/2004 15:18:17)

thanks. i read through it but it doesnt really help. I am not trying to Update/Edit existing records, but i am trying to Add NEW records to my database. I am just trying to come up with a simple way of allowing the user to do this, because i have 125 new records each week that need to be entered. I could have the user enter on the form a Date/Name/Shifts; Date/Name/Shifts etc.. 125 times, but that seems like a huge waste of time. So i am trying to figure out how i can allow the user to enter the Date once in the form; list all my names automatically from a query; and then provide an input box beside each name where the user inputs Number of Shifts. I am trying to find the simplest way of explaining it, but it is becoming a challenge.

When they click submit i want it to enter into my DB table something like:
Record#1 - Date, Name and ShiftNumber ..... then jump to the next one..
Record#2 - Date, Name and ShiftNumber .then jump to the next one... Record #3 - Date...etc etc

I want the user to only have to enter the date once on the Form, and not 125 times, cause the date will be the same for every name.

[image]local://upfiles/8498/A086D6318F04454A87157A2928700B3C.jpg[/image]



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



jaberwocky -> RE: help with a form (6/24/2004 15:58:18)

Ok that makes more sense. My coding abilities are a bit lax, but i will work with your example and see what i can come up with for my own page Thanks

[:)]



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



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



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



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



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



jaberwocky -> 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 -> RE: help with a form (6/25/2004 13:40:38)

Any error message?



jaberwocky -> 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 -> RE: help with a form (6/25/2004 15:05:33)

Strange. And you are checking the table named: ShiftsWorkedCOPY



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



jaberwocky -> RE: help with a form (6/25/2004 18:10:10)

OK that worked.. [:)]

i used the 'readonly', but then discovered when you tab thru the fields in the form it doesnt just jump down to the next "ShiftsWorked"box which is what i want.

So i am attempting to do the hidden field way, but once again, my data isnt going into the database. These are the changes i made on the form page 
<td WIDTH="120"><font SIZE="2"><input type="hidden" name="fullname<%=i%>" size="25" value="<%Response.Write(rs.Fields("FullName").Value)%>"></font></td>
    <td WIDTH="180"><font SIZE="2"><%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 am sure there is something wrong here, so that i maintain the hidden Name field and that gets written to my db, and i can just display the names in plain text to the user.



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



BeTheBall -> RE: help with a form (6/25/2004 20:44:34)

My apologies. I got confused on your field names. In shifts2.asp,

this:

IF Request("WeekOf" & i) <> "" THEN

Should be:

IF Request("ShiftsWorked" & i) <> "" THEN

I think once you make that change, you should be able to use the hidden fields. In testing I used this:

<tr>
<td WIDTH="120"><font SIZE="2"><input type="HIDDEN" name="fullname<%=i%>" value="<%Response.Write(rs.Fields("FullName"))%>"><%Response.Write(rs.Fields("FullName"))%></font></td>
<td width="58" align="center"><font color="blue"><input TYPE="text" NAME="shiftsworked<%=i%>" size="5">
</tr>

Good luck.

PS - It only took me about 2-3 hours to realize my mistake! [:@]



jaberwocky -> RE: help with a form (6/28/2004 12:11:40)

[:o] i am sorry you had to spend so much time on it :(

The form looks good now, it is what i want, but yet again, when i click submit my data isnt making it into the database. All i am getting is the "Records added" message. When u first told me to change 'disabled' to 'readonly' it worked... then i did something else and buggered it up... i tried backtracking, but still cant seem to get it to work. I am trying to look at the shifts2.asp page to see if something is wrong there but cant tell



Page: [1] 2   next >   >>

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.109375