|
| |
|
|
JimMcl
Posts: 28
Joined: 10/29/2002
Status: offline
 |
Update all fields - 12/1/2002 10:54:49
I am attempting to build an update from a form that I pre-populate with the existing data from a Access database. There are 66 fields and any or all are open for change. I did not get cryptic with field names so they are quite long (avg 10 to 15 characters)
All seems well when I only select a few fields to be updated.
When I get too long of a string or try and separate the fields onto separate lines I get error messages.
Is there a line limit that I must observe? (255 char or ...)
Can the line be split into separate lines? (what would I use to indicate there is more on the next line)
I have tried the following:
Update results set field1 = ' ::field1::' , field2 = ' ::field2::' ,
field3 = ' ::field3::' , ... fieldn = ' ::fieldn::' where ID = ' ::RID::
Update results set field1 = ' ::field1::' , field2 = ' ::field2::' ;
field3 = ' ::field3::' , ... fieldn = ' ::fieldn::' where ID = ' ::RID::'
Update results
set
field1 = ' ::field1::' ,
field2 = ' ::field2::' ,
field3 = ' ::field3::' ,
...
fieldn = ' ::fieldn::'
where ID = ' ::RID::
Is there an All parameter associated with the Update command or some other similar wild card?
As I said if I keep the number of fields low (five or six) I have no problem, but when I start getting beyond that I get syntax errors and missing operator errors.
_____________________________
Jim McLochlin
Omaha, NE
|
|
|
|
JimMcl
Posts: 28
Joined: 10/29/2002
Status: offline
|
RE: Update all fields - 12/2/2002 19:40:44
Originally, I set things up with the DRW and then I tried to go lite.
Things were working at one point and now nothing seems to work.
I pass the record ID as a hidden field in the form with RID = <%=FP_FieldVal(fp_rs," ID" )%>
I then set up the following with DRW when things quit working when I went lite and couldn' t get the DRW using the RID to work again.
UPDATE results
Set Trail_Name=' ::Trail_Name::' , Number_of_boxes=' ::Number_of_boxes::'
Where UserName=' ::UserName::' and timestamp=' ::timestamp::' and Report_Year=' ::Report_Year::'
The above isn' t modifying the correct record although no other record would have this same info. The response I get indicates that all went well.
I would prefer to use the ID (or RID) to select the field but now I keep getting length errors and or mismatch errors when I try to re-create what was working (modifying just a few fields).
_____________________________
Jim McLochlin
Omaha, NE
| |
|
|
|
JimMcl
Posts: 28
Joined: 10/29/2002
Status: offline
|
RE: Update all fields - 12/2/2002 21:27:28
mmm...
I might have found my problem. It appears that I tried to place a form inside a form. The values I thought were going to the update page (update.asp) were not. I may not be out of the woods, but at least I am starting to see some light.
Well this was probably at least one of my problems.
Now I can' t seem to get the updates to work at all.
To the update page I send with a form posting to the update page all the fields from the form that I pre-populate from the database. The user is allowed to make changes to most of the fields. I also send the record ID as a hidden field (RID).
For now, I am trying to process just one of the fields.
Here is the code that I am using on the update page:
fp_sQry=" UPDATE results Set Number_of_boxes = ' ::Number_of_boxes::' Where ID = ' ::RID::' "
Here is the error I am getting in response:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
Number: -2147217904 (0x80040E10)
Source: Microsoft OLE DB Provider for ODBC Drivers
Of course at this point everything looks right to me, but obviously that is not true. Any help would be appreciated.
< Message edited by JimMcl -- 12/2/2002 9:56:32 PM >
_____________________________
Jim McLochlin
Omaha, NE
| |
|
|
|
rdouglass
Posts: 9279
From: Biddeford, ME USA
Status: offline
|
RE: Update all fields - 12/3/2002 8:31:49
It still may be your syntax. If those foelds are both numbers, then try this:
fp_sQry=" UPDATE results Set Number_of_boxes = ::Number_of_boxes:: Where ID = ::RID::"
Generally speaking, using the DRW, the fields need to look like:
numberField = ::formFieldName::
text = ' ::formFieldName::'
dates/times = #::formFieldName::#
| |
|
|
|
JimMcl
Posts: 28
Joined: 10/29/2002
Status: offline
|
RE: Update all fields - 12/3/2002 17:30:37
Actually I think I tried just about every one of these variations, but just to make sure I tried this one to be certain:
fp_sQry=" UPDATE results Set Number_of_boxes = ::Number_of_boxes:: Where ID = ::RID::"
Yes these are both number or at least they should be.
Here is the result:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers
I did get rid of the DRW (I went lite so I could have some control).
< Message edited by JimMcl -- 12/3/2002 4:31:55 PM >
| |
|
|
|
JimMcl
Posts: 28
Joined: 10/29/2002
Status: offline
|
RE: Update all fields - 12/3/2002 22:35:05
Yes, that does work when I hardcode some numbers in. I belive I might have figured something out, or at least something to try.
I had a form bringing up all the records that belonged to a user. From that form I populated fields for each record. It looked like there were separate forms for each record. But I just now discovered that when I did the update from one form all the record ID numbers were sent. That appears to be why I had a length problem.
I am going to try and set up the table method using a update and delete field that I see on most of the sites I have visited. I belive that will result in only one record to be updated or deleted.
I' ll let you know how that goes.
Thanks for the help.
| |
|
|
|
rdouglass
Posts: 9279
From: Biddeford, ME USA
Status: offline
|
RE: Update all fields - 12/4/2002 9:15:28
Do you want to DELETE / UPDATE one at a time or do you want to do it in batches? It looks like you may have the whole results in one form (that will support batch processing). If you put just the record in a form, now you do them individually.
Basically, to do them individually, you have the <form> and </form> tags nested inside the fpdbrgn1.inc and fpdbrgn2.inc lines. To do them by batch, you' d have them outside.
I should have examples of both if you' re interested....
| |
|
|
|
JimMcl
Posts: 28
Joined: 10/29/2002
Status: offline
|
RE: Update all fields - 12/4/2002 17:24:00
Yes, I would be interested in samples. I belive I will be doing a simple one update or delete at a time.
I am trying something else to see if I can figure out where I have gone astray. Basically I am going to create a form that is populates with the record ID and then submit that to the update file.
If I hardcode a field for the record ID everything seems to work just fine. If I pass it by creating a hidden field in the form (which what I want to do, because I don' t want the user changing the record ID thereby allowing him/her to manipulate someone else' s record) then things don' t work.
Ain' t learning fun for the student and the teacher.
Ahhh... I don' t know the solution, but now maybe I understand the question!
I just passed the record id field to my form, looks good. If I use the passed parameter to find the record, that works. If I popluate the update form with the parameter and pass that to the update file that works.
If however I use a parameter in a hidden field in the form that does not work.
What does not work is to add a field RID (for record id) and assign it the value of the searched field <%=FP_FieldVal(fp_rs," ID" )%>.
If that doesn' t make sense I' ll re ask the question. At least now I understand the question.
Okay, I never figured out how to pass the record id as a number, but I figured out that if I passed it into the form and let it handle that and then use the username session variable as another part of where statement then I got what I wanted out of life. Of course if the user changes record numbers and he uses a valid one for another record he owns then he will make changes he doesn' t want to make. I have forewarned the users not to do that.
< Message edited by JimMcl -- 12/4/2002 7:20:58 PM >
| |
|
|
|
rdouglass
Posts: 9279
From: Biddeford, ME USA
Status: offline
|
RE: Update all fields - 12/5/2002 9:00:14
I don' t know if this will help, but here is how I look at using form fields in your situation. Let' s say I have a real simple 2 field DB; ' ID' and ' Text' . I build a DRW and put it on a Spooky Diet. The display portion of the DRW may look like this:
<!--#include file=" _fpclass/fpdbrgn1.inc" -->
<tr>
<td><%=FP_FieldVal(fp_rs," ID" )%></td>
<td><%=FP_FieldVal(fp_rs," Text" )%></td>
</tr>
<!--#include file=" _fpclass/fpdbrgn2.inc" -->
Now I want to edit these record(s) and submit each individually. I need to build a form around the individual records and turn them into an editable field - something like this:
<!--#include file=" _fpclass/fpdbrgn1.inc" -->
<tr>
<form method=" POST" action=" --WEBBOT-SELF--" >
<td><input type=" hidden" name=" Text" size=" 20" value=" <%=FP_FieldVal(fp_rs," ID" )%>" ><input type=" submit" value=" Submit" name=" B1" ></td>
<td><input type=" text" name=" Text" size=" 20" value=" <%=FP_FieldVal(fp_rs," Text" )%>" ></td>
</form>
</tr>
<!--#include file=" _fpclass/fpdbrgn2.inc" -->
Using this code, each record will have their own submit button and if you Request.Form(" ID" ) from a POST, you will get the individual record ID.
Now, if I wanted to submit all records (a batch mode), I can build the form around all the records like so:
<form method=" POST" action=" --WEBBOT-SELF--" >
<!--#include file=" _fpclass/fpdbrgn1.inc" -->
<tr>
<td><input type=" hidden" name=" Text" size=" 20" value=" <%=FP_FieldVal(fp_rs," ID" )%>" ></td>
<td><input type=" text" name=" Text" size=" 20" value=" <%=FP_FieldVal(fp_rs," Text" )%>" ></td>
</tr>
<!--#include file=" _fpclass/fpdbrgn2.inc" -->
<input type=" submit" value=" Submit" name=" B1" >
</form>
Note that now the <form> and </form> tags are outside the ' includes' rather than inside. If we use this code and Request.Form(" ID" ) from a POST, we now get a comma delimited string of all the ID' s in the recordset.
Does that make sense and/or help at all?
< Message edited by rdouglass -- 12/5/2002 1:39:47 PM >
| |
|
|
|
JimMcl
Posts: 28
Joined: 10/29/2002
Status: offline
|
RE: Update all fields - 12/5/2002 21:02:23
The second is pretty much what I have done and it works just fine. The only real difference is I posted it to a update file that then does an update to the database.
The only drawback is being forced to use the record id field in the form and not as a hidden field. This gives the user the potential to change a record he did not want to change. I do a logical and with the username field to make sure he won' t mess with other users records but he can still inadvertently screw up his own.
< Message edited by JimMcl -- 12/6/2002 8:28:58 PM >
| |
|
|
|
Kerry12345
Posts: 6
Joined: 8/19/2003
From: Los angeles, CA
Status: offline
|
RE: Update all fields - 8/19/2003 4:06:01
I'm having a similar problem in FrontPage 2002, although it's hyperlink related. I'm creating a "Modify" hyperlink in a DRW page. The "Modify" hyperlink will take 10 fields from one record and place them in a form page for modifying. However, after adding all 10 fields in the Query String area (under the Hyperlink Parameters window), I get the following error:
quote:
The address for this hyperlink exceeds the 255-character limit. Please reduce the number of characters.
The hyperlink address is as follows:
update.asp?ProductID=<%=FP_FieldURL(fp_rs,"ProductID")%
&ProductName=<%=FP_FieldURL(fp_rs,"ProductName")>
&ProductDescription=<%=FP_FieldURL(fp_rs,"ProductDescription")%>
&ProductSize=<%=FP_FieldURL(fp_rs,"ProductSize")%>
&CategoryID=<%=FP_FieldURL(fp_rs,"CategoryID")%>
&ReferenceID=<%=FP_FieldURL(fp_rs,"ReferenceID")%>
&UnitPrice=<%=FP_FieldURL(fp_rs,"UnitPrice")%>
&QtyInStock=<%=FP_FieldURL(fp_rs,"QtyInStock")%>
&ImageThumb=<%=FP_FieldURL(fp_rs,"ImageThumb")%>
&ImageLarge=<%=FP_FieldURL(fp_rs,"ImageLarge")%>
The problem is that even if I reduce each database field name down to 2 characters the hyperlink is still over 255 characters. Am I going about this wrong? I have tried using one primary key field in the hyperlink address, in order to propagate the form with all of the fields from the record. That does not work. I would appreciate any advice. Thanks very much.
Kerry
< Message edited by Kerry12345 -- 8/19/2003 4:27:30 AM >
| |
|
|
|
Kerry12345
Posts: 6
Joined: 8/19/2003
From: Los angeles, CA
Status: offline
|
RE: Update all fields - 8/19/2003 7:47:22
Thanks Spooky for your response. I took your advice and created a form within a single cell of my initial DRW page. All fields are hidden. Only a "Modify" button shows within the cell.
The form's code is:
<form method="POST" action="update.asp">
<input type="hidden" name="ProductID" size="20" value="<%=request ("ProductID")%>">
<input type="hidden" name="ProductName" size="50" value="<%=request ("ProductName")%>">
<input type="hidden" name="ProductDescription" size="100" value="<%=request ("ProductDescription")%>">
<input type="hidden" name="ProductSize" size="20" value="<%=request ("ProductSize")%>">
<input type="hidden" name="CategoryID" size="20" value="<%=request ("CategoryID")%>">
<input type="hidden" name="ReferenceID" size="20" value="<%=request ("ReferenceID")%>">
<input type="hidden" name="UnitPrice" size="20" value="<%=request ("UnitPrice")%>">
<input type="hidden" name="QtyInStock" size="20" value="<%=request ("QtyInStock")%>">
<input type="hidden" name="ImageThumb" size="30" value="<%=request ("ImageThumb")%>">
<input type="hidden" name="ImageLarge" size="30" value="<%=request ("ImageLarge")%>">
<p align="center"><input type="submit" value="Modify" name="B1"></p>
</form>
Under the "Form Property" window I've selected "Send to Other", "Custom ISAPI, NSAPI, CGI,....". In the "Options" area, I've entered "update.asp" for the "Action". This's the file that I want to send the data to. In the "Method" area I've selected "POST".
Unfortunately, when I click the Modify button, no data is displayed in the update.asp form page. I've tried moving the <form>, button, and </form> tags outside the include statements as you suggested to someone earlier but FrontPage simply deletes them whenever I save the file. I'm perplexed.
I would appreciate any help. Thanks very much.
Kerry
< Message edited by Kerry12345 -- 8/19/2003 7:50:25 AM >
| |
|
|
|
Kerry12345
Posts: 6
Joined: 8/19/2003
From: Los angeles, CA
Status: offline
|
RE: Update all fields - 8/21/2003 5:41:16
OK, I've figured out why I wasn't passing data from my DRW view page (admin_view.asp) to my modifying form page (update.asp). I was using
value="<%=request ("FieldName")%>">
instead of something like
value="<%=FP_FieldVal(fp_rs,"FieldName")%>">
Now the form is being populated. However, spaces are showing as "+", dashes are showing as "%2D", etc. I'd appreciate any advice. Thanks
Kerry
< Message edited by Kerry12345 -- 8/21/2003 2:56:45 AM >
| |
|
|
|
Kerry12345
Posts: 6
Joined: 8/19/2003
From: Los angeles, CA
Status: offline
|
RE: Update all fields - 8/23/2003 2:13:26
After some miserable days, I've figured this one out. When POSTing a form to another form the FP_Field() function does not encode (change) the field. This function is the one to use. On the other hand, the FP_FieldURL() function and others do encode the field. This encoding was the reason for spaces appearing as +, dashes appearing as %2D, etc. in the target form. I really wish FrontPage's help would have some documentation on these functions.
Once again, I am happy-go-lucky and no longer want to assassinate my computer. Life is gooood!!
Kerry
| |
|
 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¥ 
