Updating a Record Problem (Full Version)

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



Message

mar0364 -> Updating a Record Problem (8/3/2002 13:14:36)

I am having one heck of a hard time trying to update records. I keep following the tutorial here and looking in FP books. The only thing it does is add a record with the fields I intend to update with.
here is my code:
UPDATE comment_1
SET completed = ' completed'
WHERE ID = ' ID' ;
Setting it up so you look at a list of links. Click on link and drill to the details. On the detail page is a link to a page that performs the update statement and rederect them back to the list.

Thanks!
Rich



rdouglass -> RE: Updating a Record Problem (8/3/2002 13:21:22)

Maybe this:

UPDATE DISTINCTROW comment_1
SET completed = ' ::completed::'
WHERE ID = ::ID::;



Long Island Lune -> RE: Updating a Record Problem (8/4/2002 13:42:02)

mar0364,

What type of variable is " completed" ???

If it is a number, than ::Completed:: should work.
If it is text, than ' ::completed::' should work.

Number:
UPDATE comment_1 SET completed = ::completed:: WHERE (ID = ::ID::)

Text
UPDATE comment_1 SET completed = ' ::completed::' WHERE (ID = ::ID::)

OR...

Number:
UPDATE DISTINCTROW comment_1 SET completed = ::completed:: WHERE (ID = ::ID::)

Text
UPDATE DISTINCTROW comment_1 SET completed = ' ::completed::' WHERE (ID = ::ID::)

...like rdouglass said.

Also, I am assuming that ID is an autonumber??? If not, change that too (number or text above). If ID is NOT an autonumber, make sure it is unique. Make it INDEXED and select (YES, no duplicates). If the record already exists in your database, there should be no problem updating it.



mar0364 -> RE: Updating a Record Problem (8/4/2002 14:52:06)

Thanks everyone. The UPDATE now works. I just can' t figure out how to make it work on one record instead of all of them.

I already have the records set up so that you see a list of records click on the one you want and drill to its details that way so I am passing the " ID" there. This is where I want the option to mark it completed. How do I make it understand just that record. The DISTINCTROW. Should that do it and if so how?

Please explain or point to an example anything.

Much Appreciated.



Long Island Lune -> RE: Updating a Record Problem (8/4/2002 18:52:34)

mar0364,

1): Are you SURE the ID value is being passed???
2): Are you using the DRW to perform your SQL on the actual Display page, or are you sending the contents of the record on the Display page to a third page and performing the query there???

You really should use a third page if you don' t have one. Transfer the information from your Display page to the third page through your hyperlink. Then perform your SQL there.

If you have a third page:
Check the transfer of the variable. On the third page, place a test edit-field at the top of the page. In the " Text Properties" dialog box, name the edit-field: " ID" spelled exactly like you have it in your database table. And in the " Initial Value" field, enter:

<%=request(" ID" )%>

Now test the page and SEE if your ID appears in the edit field on page 3.

If not, try this in the " Initial Value" field:

<%=FieldURL(fp_rs, " ID" )%>

One of them should work and you should see the variable number for ID in the edit-field.

If not, then the variable is not being transferred from page to page.

If YES, then create your SQL Query on this page using the DRW, and place a link at the bottom the user clicks on to bring them back to the List page (page 1). You should not need to use DISTINCTROW, but you can use it. It won' t hurt either way.

Try this and see what happens. Use the test edit-field.



mar0364 -> RE: Updating a Record Problem (8/5/2002 10:44:36)

OK getting close. Your help has been great.

query:
UPDATE consumer_1
SET completed = ' ::completed::'
WHERE (ID = ::ID::)

ID seems to pass all 3 pages. On my first try it told me that it would not allow zero length fields. So I go back to the dbms and change that to yes.


Now it acts like it is working but does not change the field

Please take a look?
http://www.richandvince.com/data/first.asp


Thanks Bunch
Rich



Long Island Lune -> RE: Updating a Record Problem (8/5/2002 16:16:44)

Rich,

2 questions:

1): On page two, why do you have [Submit Query] and [Reset] buttons AND a hyperlink at the bottom that states " Completed" ??? This kind of confused me. Did you create a form for the test edit-field I told you about??? If you did, you do not need to enclose it in a form.

Also, you should always keep the test edit-field on page two and ad one to page three. Make them HIDDEN so nobody knows they are there. And this way there is always something on each page to intercept the value of ID. Do you know how to make them hidden??

2): In your query, your saying: SET completed = ' ::completed::'
When or where is the " Completed" variable initialized or filled with a value??? On page 2??? Is this value embedded in page 3???

' ::completed::' must contain some value right???

I assume you are using " Completed" as a flag??? 0 for no, 1 for yes??? Or something like that??? What TYPE of Variable is " Completed" ??? Text or Number???

I went to your site. It' s coming along. I could not view your source code. I tried to in EI5 but all I got was your header and tables.

Your problem does not look too difficult.
With a little more information it should be solvable
LLL



mar0364 -> RE: Updating a Record Problem (8/5/2002 16:42:20)

1): On page two, why do you have [Submit Query] and [Reset] buttons AND a hyperlink at the bottom that states " Completed" ??? This kind of confused me. Did you create a form for the test edit-field I told you about??? If you did, you do not need to enclose it in a form.

[I added this <%=request(" ID" )%> to the top of 2 and 3. Since I see the record number in both of those does that mean they are being passed along

Also, you should always keep the test edit-field on page two and ad one to page three. Make them HIDDEN so nobody knows they are there. And this way there is always something on each page to intercept the value of ID. Do you know how to make them hidden??

I' m not sure I know how to pass a hidden field in a form. Do you do it the same way?

2): In your query, your saying: SET completed = ' ::completed::'
When or where is the " Completed" variable initialized or filled with a value??? On page 2??? Is this value embedded in page 3???

When you click on completed at the bottom of 2 it is a link to 3 I thought that would initiate the update?

' ::completed::' must contain some value right???

The variable is completed

I assume you are using " Completed" as a flag??? 0 for no, 1 for yes??? Or something like that??? What TYPE of Variable is " Completed" ??? Text or Number???

Yes it is a flag. It is a text field.

Thanks;o)



Long Island Lune -> RE: Updating a Record Problem (8/5/2002 19:10:57)

Rich,

You wrote;
[I added this <%=request(" ID" )%> to the top of 2 and 3. Since I see the record number in both of those does that mean they are being passed along?]

YES, they are being passed. If you see them, then the transfer is working and the variable was passed to each page. It' s when you DON' T see them, that something is wrong. I looked at your site and saw the value in the text edit-field. So it is being passed.

Hidden Fields:
YES, just set the Font and Border colors of the test edit-field to the color WHITE and they will be invisible to your site surfers. When your doing testing, then set them to BLACK so you can see them. Once your done testing, set them back to WHITE and they will be invisilbe.

To do this:
Just double click the test edit-field and in the " Text Box Properties" dialog box, click the " Style" button and you will see Font and Border entires. Change their colors to white.

Keep in mind:
When your passing a field, to make them hidden, all you do is set their colors to white. The only thing that changes is the border and font colors. That' s what makes it HIDDEN. Everything else stays the same

On page 2, assign a value to " Completed" . Yes or No, 1 or 0. Whatever.
Pass it to page three also. Remember, you have to assign it the value you want placed in the database field " Completed" . Do you know how to do that???

Also, Maybe you should create a second text edit-field on page 3 so you can SEE the value coming into page three.

Give it the name: completed
Give it the initial value: <%=request(" completed" )%>

This will probably solve the problem.

So when you test your site and get to page 3, if you SEE the " completed" variable in the text edit-field, you' ll know it is there, being transferred correctly and you will be able to SEE it' s value. Now your SQL that states:

SET completed = ' ::completed::'

will HAVE a value in ::completed:: to work in the database.

Try this.
LLL




Long Island Lune -> RE: Updating a Record Problem (8/5/2002 19:14:08)

Rich,

Also remember, when you create a second test edit-field on page three, make sure it is above your SQL. Both test edit-fields should be as close to the top of your page, or above your SQL as possible.

LLL



mar0364 -> RE: Updating a Record Problem (8/5/2002 22:38:49)

Now I' m having trouble passing to the 3rd page. I feel like I' m there almost.

http://www.richandvince.com/data/first.asp

Thanks
[:j]



Long Island Lune -> RE: Updating a Record Problem (8/6/2002 3:07:52)

Rich,

OK, forget passing the variable for " completed" to the third page. Maybe you should just assign the value for " completed" on the third page up at the top, before the SQL. That might be easier. The only thing you need to complete your task is to assign " complete" it' s value prior to the SQL. That should do it. Yes, you are almost there.

You could just create a text edit-field at the top of page 3, then give it the Name " completed" , and the Initial Value of YES or 1. The SQL should be able to read that.

LLL



mar0364 -> RE: Updating a Record Problem (8/6/2002 13:09:19)

OK I have everything working. But for some reason the ID will not Transfer to the third. If I leave WHERE ID = ' ID' ; off of the query it changes all the records. When you look at page 3 it looks like the ID is transferring. I have both the ID and completed text boxes above the query. How can I get that ID to transfer correctly? There is a bottle of my home made Smooth Chinta in it for you. I pay shipping. Come-on Lune you got me this far.

It make me crazy.

Thanks!
Rich




Long Island Lune -> RE: Updating a Record Problem (8/6/2002 21:06:38)

Rich,

OK, we' re almost there. Would it be possible to post the code for page 3 so I can see it??? I tried to view it with IE5 but all I got was your table information. It might be easier if I could see the code for page 3. I have heard of this problem when containing a value in an edit-field and the SQL can' tt see it, it happens sometimes. I' ve had this problem too.

If you would prefer to send it to my email address instead, let me know and I' ll give it to you.
Thanks
LLL



mar0364 -> RE: Updating a Record Problem (8/6/2002 21:53:46)

Is this the code your talking about? If not let me know. Shoot me your email address. I' m ric0311@msn.com

fp_sQry=" UPDATE Consumer_1 SET completed = ' Yes' WHERE ID = ::ID:: "
fp_sDefault=" ID="
fp_sNoRecords=" <tr><td colspan=16 align=left width=" " 100%" " >No records returned.</td></tr>"
fp_sDataConn=" Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=1
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=" "
fp_sMenuValue=" "
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file=" _fpclass/fpdbrgn1.inc" -->
<!--webbot bot=" DatabaseRegionStart" endspan i-CheckSum=" 62937" -->



Spooky -> RE: Updating a Record Problem (8/6/2002 22:57:08)

Is that the code from page 3?



mar0364 -> RE: Updating a Record Problem (8/7/2002 0:00:17)

Yes



Spooky -> RE: Updating a Record Problem (8/7/2002 6:15:03)

Can you post the complete code of page 3?

Can you also confirm you have a database column called " ID" ?



mar0364 -> RE: Updating a Record Problem (8/7/2002 8:49:38)

OK I do have the column ID. The colmplete code is below. I figured out how to pass the ID to that 3rd page based on the KB article:http://support.microsoft.com/default.aspx?scid=kb;en-us;Q265166.

Now here is the odd thing. If the completed column has text in it the update statement removes the text but does not add anything. If the completed column has nothing in it, it still adds nothing.

<html>

<head>
<meta name=" GENERATOR" content=" Microsoft FrontPage 5.0" >
<meta name=" ProgId" content=" FrontPage.Editor.Document" >
<meta http-equiv=" Content-Type" content=" text/html; charset=windows-1252" >
<title>New Page 1</title>
</head>

<body>
<form method=" POST" action=" --WEBBOT-SELF--" >
<!--webbot bot=" SaveResults" u-file=" _private/form_results.csv" s-format=" TEXT/CSV" s-label-fields=" TRUE" startspan --><input TYPE=" hidden" NAME=" VTI-GROUP" VALUE=" 0" ><!--webbot bot=" SaveResults" endspan i-checksum=" 43374" --><p>
<input type=" text" name=" completed" size=" 20" value=" <%=(" completed" )%>" ></p>
<p>
<input type=" text" name=" ID" size=" 20" value=" <%=request(" ID" )%>" ></p>
</form>







<table width=" 100%" border=" 1" >
<thead>
</thead>
<tbody>
<!--webbot bot=" DatabaseRegionStart" s-columnnames s-columntypes s-dataconnection=" Database1" 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=" FALSE" s-recordsource s-displaycolumns s-criteria s-order s-sql=" UPDATE Consumer_1<br>SET completed = ' ::Yes::' <br>WHERE ID = ::ID:: <br>" b-procedure=" FALSE" clientside suggestedext=" asp" s-defaultfields=" Yes=&amp;ID=" s-norecordsfound=" No records returned." i-maxrecords=" 256" i-groupsize=" 1" 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.</font></td></tr>" b-WasTableFormat=" TRUE" 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=" UPDATE Consumer_1 SET completed = ' ::Yes::' WHERE ID = ::ID:: "
fp_sDefault=" Yes=&ID="
fp_sNoRecords=" <tr><td colspan=16 align=left width=" " 100%" " >No records returned.</td></tr>"
fp_sDataConn=" Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=1
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=" "
fp_sMenuValue=" "
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file=" _fpclass/fpdbrgn1.inc" -->
<!--webbot bot=" DatabaseRegionStart" endspan i-CheckSum=" 28103" --><!--webbot bot=" DatabaseRegionEnd" b-tableformat=" TRUE" b-menuformat=" FALSE" u-dbrgn2=" _fpclass/fpdbrgn2.inc" i-groupsize=" 1" clientside tag=" TBODY" preview=" <tr><td colspan=64 bgcolor=" #FFFF00" align=" left" width=" 100%" ><font color=" #000000" >This is the end of a Database Results region.</font></td></tr><TR><TD ALIGN=LEFT VALIGN=MIDDLE COLSPAN=64><NOBR><INPUT TYPE=Button VALUE=" |< " ><INPUT TYPE=Button VALUE=" < " ><INPUT TYPE=Button VALUE=" > " ><INPUT TYPE=Button VALUE=" >| " > [1/1]</NOBR><BR></td></tr>" startspan --><!--#include file=" _fpclass/fpdbrgn2.inc" -->
<!--webbot bot=" DatabaseRegionEnd" endspan i-CheckSum=" 62730" --></tbody>
</table>







<p> </p>







<p><a href=" first.asp" >Back to the list.</a></p>


</body>

</html>



Long Island Lune -> RE: Updating a Record Problem (8/7/2002 11:53:26)

Rich,

OK, so ID was an autonumber (NOT Text)???
Your close now.
Two more things:


1): Your SQL:
This is what you have listed:

fp_sQry=" UPDATE Consumer_1 SET completed = ' ::Yes::' WHERE ID = ::ID:: "

You have it like there is a field named " Yes" . You don' t have a test edit-field named: " Yes" , it' s a value not a field, so change it to say the following:

fp_sQry=" UPDATE Consumer_1 SET completed = ' Yes' WHERE ID = ::ID:: "

Try running it now and see what happens....
If it still does not work, then proceed with strep 2 below:

2): We can see that the ID variable is coming into the page. Now that you know it is there, and there is a webbot that wrapped itself around the two test edit-fields like I mentioned in the email I sent to your email box last night, lets try REMOVING the test edit-fields for " ID" and " Completed" and see what happens. We KNOW the ID value is being passed from page 2 correctly, AND we know that you are manually assigning " Yes" to " Completed" right inside the SQL, AND we know that the " pest" webbot wrapped the edit-fields, so simply remove the test edit fields for " ID" and " Completed" . And see what happens.

Try 1 (above) first, then test. If it doesnot work, then try 2 (above).
This should solve the problem.
If not, let me know.
LLL





LLL



Long Island Lune -> RE: Updating a Record Problem (8/7/2002 11:58:34)

Rich,

One more thing, is page 3 an .html or an .ASP type page??? In your code I saw:

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

This was generated when the webbot wrapped itself around your test edit-fields. Once you remove them, this message and the problem at hand should automatically disappear.
LLL



mar0364 -> RE: Updating a Record Problem (8/7/2002 16:33:44)

HOOORAAAAYYY!!!!!!!!!!!!!!!!!!!

No.1 worked.

OK I' m going to summarize this and put it in the Community tips. WOW

I feel like a million bucks!

Thanks to LLL and Spook!!!
Rich Mariner



Long Island Lune -> RE: Updating a Record Problem (8/7/2002 22:09:05)

Rich,

Fantastic... I' m glad everything worked for you.

Long Island Lune



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
9.423828E-02