Creating an Update Query using Frontpage (Full Version)

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



Message

Kage -> Creating an Update Query using Frontpage (12/2/2003 17:28:21)

Is there any easy way to create a simple update query in Frontpage that would allow a person to query a large number of results into view and allow a person to change data for those results while viewing them?



BeTheBall -> RE: Creating an Update Query using Frontpage (12/2/2003 21:49:55)

Sure. Are you talking about editing several records at once or one at a time? For example, I have a page that brings up a list of records and to the right of each record is a hyperlink titled "Edit". The link takes the user to a form that is prepopulated with the current values. The user then can change any info he/she wants, click submit and the record is then updated. Is that somewhat what you mean???



Kage -> RE: Creating an Update Query using Frontpage (12/2/2003 22:07:39)

That's pretty much exactly what I'm talking about.

The situation is that I have two people who pack only international orders from my facility. They need to see the ones that are considered priority that come from our customer. I have a form in place to populate a database with the incoming information and a results page to populate exactly what he needs to see (hence my date question from earlier) for only that day. I need him to be able to mark these complete as he goes in order to get them off the list and show that he's done his part.

Basically what you said is exactly what I need. Though I'm pretty out of hand with all this.



Kage -> RE: Creating an Update Query using Frontpage (12/3/2003 14:34:02)

I have an option in the database itself that is basically a checkbox for whether it's completed or not. I would like to be able to add a button that would just check it and take it off the list.



Spooky -> RE: Creating an Update Query using Frontpage (12/3/2003 15:26:17)

This was a similar discussion:
http://www.frontpagewebmaster.com/m-127398/mpage-1/key-checkbox%252Cmultiple/anchor/tm.htm#127398

Using the "IN" operator, you can update many records using the ID of the selected records.
A comma delimited string is generated by the checkboxes.
Perfect for your purpose.



Kage -> RE: Creating an Update Query using Frontpage (12/3/2003 18:47:27)

Thanks for the direction spooky, that gave a little insight into the situation i'm working on. I'd still like to hear how duane accomplished this, the idea of the button appeals to the keeping it simple aspect of what this needs to do.

your diet technique worked wonders for me btw, great work and a huge thank you.



BeTheBall -> RE: Creating an Update Query using Frontpage (12/3/2003 20:38:50)

quote:

the idea of the button appeals to the keeping it simple aspect of what this needs to do.


Depends on what you mean by simplistic. Coding wise, neither solution is overly easy. If I understand what Spooky is saying you would have a page with a table showing all the day's records. Beside each record would be a checkbox that the person doing the shipping could check as he took care of each item. Once finished with the orders, he would still have a submit button that he could click and all records with a checked box would be updated simultaneously. For the end user, quite a simple process. Consider it similar to deleting messages out of your hotmail account. You check every message you want to delete and then click a button. This would be the same. The user would check each item he shipped and then click a button which would go to an update script to update the records in the db.

What I proposed would add a hyperlink beside each record. The link would take the user to a details page wherein the details of the record could be displayed along with perhaps a dropdown or a check box where the worker could select the new status of the order, for example, "Completed" and then submit updating that single record. He could then be redirected to the results page where he could select the next record to be updated.

Perhaps the only advantage I can think of to the method I proposed is less chance for error. When doing a mass update, it is a little more likely that a box may be checked or not checked in error and then that record would not be updated. In the case of a record marked complete in error, you would need to create a page that would enable one to go in and correct that.

I am more than willing to talk you through how to create a details page and a page for the update statement, but only if you are sure that is the route you want to go. It will involve two pages including a custom form and some custom SQL to perform the update. Spooky's suggestion is probably easier to implement. It just depends on whether you want the employees working with one record at a time or doing mass updating. Only you know which is best for you.



Kage -> RE: Creating an Update Query using Frontpage (12/4/2003 11:23:52)

After speaking with the guys who'll be using it, the checkbox solution is probably best. I'll need the page displaying their orders to be a results page though. It will basically just show on a day by day basis, always the current date. There will never be a need to show any date other than the current one.

Give what you've said is it possible to create a results page with a submit button on it that would clear off work as they go?



Kage -> RE: Creating an Update Query using Frontpage (12/4/2003 15:43:53)

I would like to try your idea Duane, the room for error here basically has to be 0%. These orders these guys do are basically emergency situations. After reviewing the options with their supervisor, this would probably be best because they will inevitably screw up with the multiple checkboxes.

Whenever you have time and if you are still willing, please, lend me a hand.

Thanks.
Kage



BeTheBall -> RE: Creating an Update Query using Frontpage (12/4/2003 17:33:31)

You said your results page is already complete, right? You will want to make sure your SQL excludes records that are already marked as complete in the db.

I think I will have you go a little different route than what I originally said.

Let's do this in steps as it may be a little involved. I am assuming you have a list of headings in the first row and then your results below. If so, add a column to the right side of the table. In the heading for the new column enter "Completed?" or whatever you want to call it. Then, assuming you have dieted the code, place a hyperlink in the cell below your heading. To do that, type something like, "Mark Completed" in the cell and then in html view replace Mark Completed with this code:

<a href="#" onclick="javascript:window.open('UpdateForm.asp?ID=<%=FP_FieldVal(fp_rs,"ID")%>', 'none', 'width=400,height=400');">Mark Completed</a>

This will now open UpdateForm.asp in a new window.

The next step will be to create UpdateForm.asp. Create a new page and insert a database results region displaying only the ID field of your db. In step 3 click options and set a criteria of ID = ID. In step 4 of the wizard, select List - One field per item and under List Options choose Paragraphs. Clear the horizontal separater between records box. In Step 5 clear the create search form box. So, now when you click a link on the results page it will open UpdateForm.asp in a new window and should display the ID number of the record that appeared in the same row as the hyperlink you clicked.

See if you can get through the above and once that is working I will go over the rest. By the way, do NOT diet the database results you put on updateform.asp. Just so you know where I am going with this, I will have a link that opens updateform.asp in a new window. From there there will eventually be a submit button that will go to a page where the update SQL will execute. That window will then close and the results page will refresh so the recently updated record will then no longer appear in the results. Let me know when you finish what I have posted so far, or if you run into problems.

Good luck.



Kage -> RE: Creating an Update Query using Frontpage (12/4/2003 18:46:37)

I'll get on this in the morning, I'm in no mental shape to be dealing with it at the moment.

You're a life/timesaver man. I can't even being to express how big of a help you've been.



BeTheBall -> RE: Creating an Update Query using Frontpage (12/4/2003 19:15:35)

Yes, three hours sleep is not near enough when dealing with this. You have really dived in head first. Good luck on it and feel free to keep asking questions. I like helping out as I feel indebted to those who helped me a lot in the beginning and quite a bit still. See you in the morning. ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ



Kage -> RE: Creating an Update Query using Frontpage (12/5/2003 16:23:51)

Good afternoon Duane,

Having never manually built a drw table, I am unsure as to where the hyperlink you suggest needs to go.

<title>Export Must Ship Report</title>
</head>

<body>

<table width="100%" border="1">
<thead>
<tr>
<td><b>ID</b></td>
<td><b>Date</b></td>
<td><b>Time</b></td>
<td><b>RyderCsr</b></td>
<td><b>Pickticket</b></td>
<td><b>Status</b></td>
<td><b>Customer</b></td>
<td><b>Consolodation</b></td>
<td><b>Comments</b></td>
<td><b>Completed</b></td>
</tr>
</thead>
<tbody>
<!--webbot bot="DatabaseRegionStart" s-columnnames="ID,Date,Time,WichitaCsr,RyderCsr,Pickticket,Status,Customer,Consolodation,Comments,Completed" s-columntypes="3,135,135,202,202,3,202,202,11,202,11" s-dataconnection="ExportMustShipLog" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="TRUE" s-recordsource s-displaycolumns="ID,Date,Time,RyderCsr,Pickticket,Status,Customer,Consolodation,Comments" s-criteria s-order s-sql="SELECT * FROM Results WHERE (Date = Date() AND Completed IS NULL) ORDER BY Time ASC" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields s-norecordsfound="No records returned." i-maxrecords="256" i-groupsize="0" botid="0" u-dblib="../_fpclass/fpdblib.inc" u-dbrgn1="../_fpclass/fpdbrgn1.inc" u-dbrgn2="../_fpclass/fpdbrgn2.inc" tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00" align="left" width="100%"><font color="#000000">This is the start of a Database Results region. The page must be fetched from a web server with a web browser to display correctly; the current web is stored on your local disk or network.</font></td></tr>" startspan --><!--#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 Results WHERE (Date = Date() AND Completed IS NULL) ORDER BY Time ASC"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=9 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="ExportMustShipLog"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=9
fp_fCustomQuery=True
BOTID=0


somewhere after this is all the webbot stuff in tables that gets dieted out. Is it somewhere in that bad gibberish?



BeTheBall -> RE: Creating an Update Query using Frontpage (12/5/2003 18:46:31)

OK, back to this one. Open your results page in Normal View (not HTML view). Select the right-most column in the table. Then go to the top menu and select "Table", then "Insert", "Rows or Columns" then finally, choose 1 column right of selection. Then your hyperlink will go in the cell next to Completed. Does that help?



Kage -> RE: Creating an Update Query using Frontpage (12/8/2003 15:12:58)

I've gone all the way through your steps and am ready when you are to go to the next one if you have time. Much appreciated.

L



BeTheBall -> RE: Creating an Update Query using Frontpage (12/8/2003 16:21:01)

Go back to the form you created in UpdateForm.asp and in Normal view, right-click it. Choose "Form Properties". Check the "Send to other" radio button. Then click the options button. In the Action box, type Update.asp

Now you need to create a new page. Call it update.asp. On that page, insert a Database Results Wizard. In step 2, choose custom query with this as your query:

UDDATE Results SET Completed='Yes' WHERE ID=::ID::

Finish the rest of the wizard and then test the whole series of pages to ensure the update is working. Once we have that working, we'll see if we can add some code to close the popup and refresh the results page (I haven't done that part before, but think I found some code that will work.)



Kage -> RE: Creating an Update Query using Frontpage (12/8/2003 17:32:02)

To build the UpdateForm.asp page the DRW wizard was used and there is no Form Properties on the right-click options, just page properties. Is there a way to get this to turn into a form?



BeTheBall -> RE: Creating an Update Query using Frontpage (12/8/2003 18:11:04)

Oops! I forgot a step. What does your code look like for UpdateForm.asp? Please post it.



Kage -> RE: Creating an Update Query using Frontpage (12/8/2003 18:24:02)

LOL
I was looking at this over and over going "huh?"

here's the code.


UpdateForm.asp


<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<% ' FP_ASP -- ASP Automatically generated by a Frontpage Component. Do not Edit.
FP_CharSet = "windows-1252"
FP_CodePage = 1252 %>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>

<body>

<!--webbot bot="DatabaseRegionStart" s-columnnames="ID,Date,Time,WichitaCsr,RyderCsr,Pickticket,Status,Customer,Consolodation,Comments,Completed" s-columntypes="3,135,135,202,202,3,202,202,11,202,11" s-dataconnection="ExportMustShipLog" b-tableformat="FALSE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="FALSE" i-listformat="0" b-makeform="FALSE" s-recordsource="Results" s-displaycolumns="ID" s-criteria="{ID} EQ {ID} +" s-order s-sql="SELECT * FROM Results WHERE (ID = ::ID::)" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="ID=" s-norecordsfound="No records returned." i-maxrecords="256" i-groupsize="0" botid="0" u-dblib="../_fpclass/fpdblib.inc" u-dbrgn1="../_fpclass/fpdbrgn1.inc" u-dbrgn2="../_fpclass/fpdbrgn2.inc" tag="BODY" preview="<table border=0 width="100%"><tr><td bgcolor="#FFFF00" align="left"><font color="#000000">This is the start of a Database Results region. The page must be fetched from a web server with a web browser to display correctly; the current web is stored on your local disk or network.</font></td></tr></table>" startspan --><!--#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 Results WHERE (ID = ::ID::)"
fp_sDefault="ID="
fp_sNoRecords="No records returned."
fp_sDataConn="ExportMustShipLog"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="20220" --><p><b>ID:</b> <!--webbot bot="DatabaseResultColumn" s-columnnames="ID,Date,Time,WichitaCsr,RyderCsr,Pickticket,Status,Customer,Consolodation,Comments,Completed" s-column="ID" b-tableformat="FALSE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>ID<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"ID")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="62813" --></p>
<!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE" b-menuformat="FALSE" u-dbrgn2="../_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside tag="BODY" preview="<table border=0 width="100%"><tr><td bgcolor="#FFFF00" align="left"><font color="#000000">This is the end of a Database Results region.</font></td></tr></table>" startspan --><!--#include file="../_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-checksum="56926" --></body></html>



BeTheBall -> RE: Creating an Update Query using Frontpage (12/8/2003 18:39:23)

Ok. In normal view, put your cursor just to the right of <ID> and click "Enter" to insert a line. Then on the new line, Click Insert, then Form. Right click the form and click Advanced. Then click the Add button. The form field name is ID and the value should be <%=Request.Form("ID")%>. Do that first and then follow the rest of my other post about choosing Form Properties and marking the "Send to Other" option. Sorry about that. [&o]



Kage -> RE: Creating an Update Query using Frontpage (12/8/2003 19:52:24)

When building that SQL statement it tries to verify and tells me that there is an error with it.

The error message reads:

Server error: Unable to retrieve schema information from the query:

UDDATE Results SET Completed='Yes' WHERE ID=1

against a database using the connection string

DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=fpdb/ExportMustShipLog.mdb.

The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147217900 (0x80040e14)



Kage -> RE: Creating an Update Query using Frontpage (12/8/2003 20:09:25)

LOL
nevermind, i caught it, spelling mistake in update, rolling along nicely so far.



BeTheBall -> RE: Creating an Update Query using Frontpage (12/8/2003 21:51:39)

Rounding third and heading for home. Add this to somewhere between <head> and </head> in update.asp:

<meta http-equiv="Refresh" content="0; URL=RefreshParent.htm">

Then download the attached file and save it in the same folder as your other pages (changing the extension to .htm). That should do it. The attached file has the code to close the popup and refresh the parent. Good luck.



Kage -> RE: Creating an Update Query using Frontpage (12/11/2003 18:05:53)

okay, this damn report is about to drive me nuts. I can't get it to work.
If I use the DRW wizard to make it, and choose one of the Criteria to be "Completed = 0", which in access would denote that the checkbox is not checked, I get a data type mismatch error. If I change the database field to Text with a default value of No and then set the DRW critera to match it by making it say "Completed = No" i get no error message, I simply don't get any results period.

help?



BeTheBall -> RE: Creating an Update Query using Frontpage (12/11/2003 19:02:30)

I've never had much luck with checkboxes in Access. However, try:

"Complete=Null"

To use text, the statement needs single quotes around No so it should look like this:

"Completed='No'"



Kage -> RE: Creating an Update Query using Frontpage (12/12/2003 19:27:08)

I'm actually going to start this over again Monday with a new form and Database. I'm going to have the same method of determining whether it's closed or not that I have on the other form/db you helped me with where it displays anything without a confirmation signature (signature = null). I'd like to apply this same method you've been guiding me through here on this new one if possible.

I've tried this on another Form in another area with good results so far. I've gotten the report to work, which searches by a ticket number and has the Complete hyperlink in the last field on the table. I'm just not sure how I would go about making it pull the same form up from there with all the data associated with that particular ticket and then allow me to update it. Any suggestions?

You've been a huge help man. I can't even being to thank you enough.

Kage



BeTheBall -> RE: Creating an Update Query using Frontpage (12/12/2003 20:20:23)

quote:

I'm just not sure how I would go about making it pull the same form up from there with all the data associated with that particular ticket and then allow me to update it. Any suggestions?


Not sure I follow what you are saying. If you have a results page, you can always create a link to another page containing a form created with the drw. That is why we include the ID number of the record in the hyperlink so that the ID is passed to the next page and is used as the criteria for the SQL statement.



Kage -> RE: Creating an Update Query using Frontpage (12/15/2003 15:04:27)

I need the DRW to have a link that will call up the record for a result and allow you to update it with the same form you used to put it in (speaking design wise here) and allow you to update that record. Sorry for being vague.

Good weekend?



BeTheBall -> RE: Creating an Update Query using Frontpage (12/15/2003 20:51:38)

I need the DRW to have a link that will call up the record for a result and allow you to update it with the same form you used to put it in (speaking design wise here) and allow you to update that record. Sorry for being vague.

Guess it's the part in bold that I am not understanding. Are you saying that you want the Form that updates a record to be the same as the one that creates a new one? If so, does it have to be the same form or can the just look alike?



Kage -> RE: Creating an Update Query using Frontpage (12/16/2003 11:20:15)

It doesn't have to be the same form, I would just like to keep them looking the same so as not to confuse the user. Sorry for the poor wording.



Page: [1] 2   next >   >>

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.1411133