Update multiple entry of database

pinto19 · Jul 22, 2005

Hi all, hoping you could help me with this. I have created several pages using MS FrontPage that has forms. Once the forms are submitted, they are saved to a database. On the entry page I have 6 text boxes of which only 5 are visible. So people accessing the entry.asp page can only fill those 5 boxes. I have a report.asp page that I also created using FP by doing insert, DB, Results and I can see all the entry that has been made by the entry.asp page. Some one looks at those entries and processes those requests. Now I need to somehow be able to go in and make updates to those record in the filed called "completedby" entering their initial (this is the text box that I made invisible in the entry.asp page)
I have used the MS FP help and was able to update on record at a time. However I get about 500 entries each day. I would like to know if there are any ways that I can say just put check marks next to the records and be able to update those along with adding my initial on the completedby field.
This would cut down lot of time spent on this. This way I could just give the link to this page to someone who normally doesnt have access to the database file.

Thanks for your help.

DotNetGnat · Jul 22, 2005

I would do something like this:

Step 1:

entry.asp form...

This form has 5 entry text boxes on which the user can enter all the required details and submit it to the database

lets say process.asp....

on this process.asp we collect all these 5 entries and submit it to the database...

for example our database looks something like this with the following fields

1.RequestId
2.RequestName
3.RequestDate
4.RequestedBy
5.RequestDesc
6.RequestCompletedBy
7.ActiveInd

now first 5 fields are the ones that the user submits through the entry.asp page....at this stage our 6th field will be empty and our 7th field will be 1. 1 indicates that the request is active and not acted upon by...

NOW moving on to step 2:

lets say display.asp

on this page we will display all the active or open request...we will show first 5 fields with RequestId being a link which can be used by the person who process or solves request....

step 3: once the particular RequestId link is clicked it shows all the five fields and also shows the 6th field in which the person processing the request can enter his name and submit.

once this issubmitted we will use a finalprocess.asp page and update our database with the completedBy person for this particular record and set our 7th field ActiveInd to 0 indicating that it is not active any more.

i hope the above description is clear enough...if not please post back...also ask questions if have any problems conding this logic...

-DNG

pinto19 · Jul 23, 2005

Thanks for your reply. If I understand this right, we will still have to update one record at time in step 3, right? I need to be able to update lets say 15 or 20 records at a time. Say we will have a update.asp page which will show 15/20 records at a time from the submitted records, and once those requests are processed, one of us can go and put our initial to indicate that the request is complete and submit to update all 15/20 records at a time.

As far as coding goes, I have never done any asp coding. I have created everything using FP which does the coding automatically. But I am willing to to dable into it.

Again thanks for your help.

DotNetGnat · Jul 23, 2005

Yes you understood it correctly...according to my step 3 we will be updating only one record at a time...now that you say you want to update multiple records at a time...

now the first thing you need to identify which 15 or 20 records to update or is it that you want to display all the active/unsolved records with a text box and a check box besides them.

if you do so the above logic will still hold good.

in the display.asp you will display all the 15(lets say) records with a check box(that can be checked to indicate the record is processed) and a textbox( that can hold the initials of the person who processed the record)

and you will need to code to loop thru all the check box values to see if they are checked and then collect all the corresponding requestid and update them with the initials of the person who processed that particular request...

I am aware that FP has some database wizard but i have never worked on it...so i cant be of much help there...but if you write some asp code and facing any errors or problems i would be glad to help...

-DNG

pinto19 · Jul 23, 2005

Ok, here is what I have.

Codes for the entry page:

<%
' FP_ASP ASP Automatically generated by a Frontpage Component. Do not Edit.
On Error Resume Next

strErrorUrl = ""

If Request.ServerVariables("REQUEST_METHOD") = "POST" Then
If Request.Form("VTI-GROUP") = "0" Then
Err.Clear

Set fp_conn = Server.CreateObject("ADODB.Connection")
FP_DumpError strErrorUrl, "Cannot create connection"

Set fp_rs = Server.CreateObject("ADODB.Recordset")
FP_DumpError strErrorUrl, "Cannot create record set"

fp_conn.Open Application("enterexceptions_ConnectionString")
FP_DumpError strErrorUrl, "Cannot open database"

fp_rs.Open "Results", fp_conn, 1, 3, 2 ' adOpenKeySet, adLockOptimistic, adCmdTable
FP_DumpError strErrorUrl, "Cannot open record set"

fp_rs.AddNew
FP_DumpError strErrorUrl, "Cannot add new record set to the database"
Dim arFormFields0(10)
Dim arFormDBFields0(10)
Dim arFormValues0(10)

arFormFields0(0) = "complby"
arFormDBFields0(0) = "complby"
arFormValues0(0) = Request("complby")
arFormFields0(1) = "project"
arFormDBFields0(1) = "project"
arFormValues0(1) = Request("project")
arFormFields0(2) = "reason"
arFormDBFields0(2) = "reason"
arFormValues0(2) = Request("reason")
arFormFields0(3) = "supv"
arFormDBFields0(3) = "supv"
arFormValues0(3) = Request("supv")
arFormFields0(4) = "from"
arFormDBFields0(4) = "from"
arFormValues0(4) = Request("from")
arFormFields0(5) = "to"
arFormDBFields0(5) = "to"
arFormValues0(5) = Request("to")
arFormFields0(6) = "name"
arFormDBFields0(6) = "name"
arFormValues0(6) = Request("name")
arFormFields0(7) = "code"
arFormDBFields0(7) = "code"
arFormValues0(7) = Request("code")
arFormFields0(8) = "date"
arFormDBFields0(8) = "date"
arFormValues0(8) = Request("date")
arFormFields0(9) = "emplid"
arFormDBFields0(9) = "emplid"
arFormValues0(9) = Request("emplid")

FP_SaveFormFields fp_rs, arFormFields0, arFormDBFields0

If Request.ServerVariables("REMOTE_HOST") <> "" Then
FP_SaveFieldToDB fp_rs, Request.ServerVariables("REMOTE_HOST"), "Remote_computer_name"
End If
If Request.ServerVariables("HTTP_USER_AGENT") <> "" Then
FP_SaveFieldToDB fp_rs, Request.ServerVariables("HTTP_USER_AGENT"), "Browser_type"
End If
FP_SaveFieldToDB fp_rs, Now, "Timestamp"
If Request.ServerVariables("REMOTE_USER") <> "" Then
FP_SaveFieldToDB fp_rs, Request.ServerVariables("REMOTE_USER"), "User_name"
End If

fp_rs.Update
FP_DumpError strErrorUrl, "Cannot update the database"

fp_rs.Close
fp_conn.Close

Session("FP_SavedFields")=arFormFields0
Session("FP_SavedValues")=arFormValues0
Response.Redirect "sent.htm"

End If
End If

%>
<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns

="urn:schemas-microsoft-com

ffice

ffice" xmlns="

http://www.w3.org/TR/REC-html40">

<head>
<script>
function StartDate(){

/*Current date in form credit:
JavaScript Kit (

http://www.javascriptkit.com)

Over 200+ free scripts here!
*/

var mydate=new Date()
var theyear=mydate.getYear()
if (theyear < 1000)
theyear+=1900
var theday=mydate.getDay()
var themonth=mydate.getMonth()+1
if (themonth<10)
themonth="0"+themonth
var theday=mydate.getDate()
if (theday<10)
theday="0"+theday

//////EDIT below three variable to customize the format of the date/////

var displayfirst=themonth
var displaysecond=theday
var displaythird=theyear

////////////////////////////////////

document.dform.date.value=displayfirst+"/"+displaysecond+"/"+displaythird
}
</script>
<script type="text/javascript">

function handleEnter (field, event) {

var keyCode = event.keyCode ? event.keyCode : event.which ? event.which : event.charCode;

if (keyCode == 13) {

var i;

for (i = 0; i < field.form.elements.length; i++)

if (field == field.form.elements)

break;

i = (i + 1) % field.form.elements.length;

field.form.elements.focus();

return false;

}

else

return true;

}

</script>

<meta http-equiv="Content-Language" content="en-us">
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Daily Exceptions</title>
</head>

<BODY onLoad="StartDate()" ;>
<p align="center">
 </p>
<p align="center"><b><u><font size="6" color="#FF0000">Daily Exceptions</font></u></b></p>
<form method="POST" action="--WEBBOT-SELF--" name="dform">
<input TYPE="hidden" NAME="VTI-GROUP" VALUE="0"><p align="center">
<b>Date: </b>
<input type="text" onkeypress="return handleEnter(this, event)" size="20" name="date"></p>
<p align="center"><b>Employee ID: </b>
<input type="text" onkeypress="return handleEnter(this, event)" size="6" name="emplid"></p>
<p align="center"><b>Name: </b>
<input type="text" onkeypress="return handleEnter(this, event)" size="20" name="name"></p>
<p align="center"> </p>
<p align="center"> </p>
<p align="center"><b>Exception Code: <select size="1" name="code">
<option>Pick from the list</option>
<option>PBRK1-Paid Break 1</option>
<option>PBRK2-Paid Break 2</option>
<option>Lunch-Unpaid Lunch</option>
<option>FOCUS-Meeting/CQE/QA</option>
<option>OT-Overtime</option>
<option>LATE-Late to start</option>
<option>FTC-Leave Early</option>
<option>PTO-Paid Time Off</option>
</select></b></p>
<p align="center"><b>From:
</b>
<input type="text" onkeypress="return handleEnter(this, event)" size="5" name="from"> <b>To: </b>
<input type="text" onkeypress="return handleEnter(this, event)" size="5" name="to"> </p>
<p align="center"><b>Project: <select size="1" name="project">
<option>Pick from the list</option>
<option>103</option>
<option>108</option>
<option>122</option>
<option>179</option>
<option>101</option>
<option>385</option>
<option>404</option>
<option>785</option>
<option>872</option>
</select></b></p>
<p align="center"><b>Reason: </b>
<input type="text" onkeypress="return handleEnter(this, event)" size="34" name="reason"></p>
<p align="center">
<input type="text" name="supv" size="2" style="border: 1px solid #FFFFFF; padding-left: 4; padding-right: 4; padding-top: 1; padding-bottom: 1"><input type="text" name="complby" size="2" style="border: 1px solid #FFFFFF; padding-left: 4; padding-right: 4; padding-top: 1; padding-bottom: 1"><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></p>
</form>
<p align="center"> </p>
</body>

</html>

The following page shows the ones that needs to be completed.

<html>

<head>
<META HTTP-EQUIV="REFRESH" CONTENT="60 ; url= verpt.asp">
<meta http-equiv="Content-Language" content="en-us">
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>View Agent Submitted Exceptions</title>
</head>

<body>

<p align="center"> </p>
<p align="center"><font size="4" color="#0000FF">Agent Submitted Exceptions
Report</font></p>
<table width="100%" border="1" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" cellpadding="0">
<thead>
<tr>
<td align="center"><b>#</b></td>
<td align="center"><b>Date</b></td>
<td align="center"><b>Employee ID</b></td>
<td align="center"><b>Project</b></td>
<td align="center"><b>Name</b></td>
<td align="center"><b>Code</b></td>
<td align="center"><b>From</b></td>
<td align="center"><b>To</b></td>
<td align="center"><b>Reason</b></td>
<td align="center"><b>Supervisor<br>Approval</b></td>
</tr>
</thead>
<tbody>

<% 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 ""wfm report"" ORDER BY date DESC,emplid ASC"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=10 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="enterexceptions"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="ID"
fp_sMenuValue="ID"
fp_sColTypes="&ID=3&date=202&emplid=202&name=202&code=202&from=202&to=202&reason=202&supv=202&complby=202&project=202&"
fp_iDisplayCols=10
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

<tr>
<td align="center">
<%=FP_FieldVal(fp_rs,"ID")%></td>
<td align="center">
<%=FP_FieldVal(fp_rs,"date")%></td>
<td align="center">
<%=FP_FieldVal(fp_rs,"emplid")%></td>
<td align="center">
<%=FP_FieldVal(fp_rs,"project")%></td>
<td align="center">
<%=FP_FieldVal(fp_rs,"name")%></td>
<td align="center">
<%=FP_FieldVal(fp_rs,"code")%></td>
<td align="center">
<%=FP_FieldVal(fp_rs,"from")%></td>
<td align="center">
<%=FP_FieldVal(fp_rs,"to")%></td>
<td align="center">
<%=FP_FieldVal(fp_rs,"reason")%></td>
<td align="center">
<%=FP_FieldVal(fp_rs,"supv")%></td>
</tr>

</tbody>
</table>
<p align="center"> </p>

</body>

</html>

This is where I would like to be able to show 15 records at a time and mark as completed.

Thanks

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Update multiple entry of database

pinto19

Vendor

DotNetGnat

Programmer

pinto19

Vendor

DotNetGnat

Programmer

pinto19

Vendor

Similar threads

Part and Inventory Search

Sponsor