Updating database records (Full Version)

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



Message

pmagas -> Updating database records (10/19/2005 18:36:36)

Is there a standard, easy or built-into-FP way to have a form come up - already filled in with the results of a particular database record so that a user can update that record?

I need a search first - by one of the fields in the form. Then I identify that record using an ID number similar to what Spooky has us do here? Somewhere in there I have it feed to another page that has the same form I used for entry into the database, except using the record number, the fields are filled in.

Does that sound reasonable?? If so, how do I get each field to populate with that record's information?

Thank you for any direction or guidance you can offer me.

Cheers!

PS - I put the drw page on the Spooky diet and saved before changing the form details. When I change the form details, it's not actually changing anything... Did I do the Spooky diet too soon?



BeTheBall -> RE: Updating database records (10/19/2005 19:03:02)

Create a page to display the results, but in step 4 of the wizard choose the formatting option of:

List - one field per item

Then, under List options, choose Text fields. Then finish the wizard. That will give you a text box for each field pre-filled with the db value for that field. You would then edit the form properties to submit to a page containing your update SQL.

Hope that helps. One you have the form, you can then edit it to do things like replace the text boxes with text areas or dropdowns, etc.



pmagas -> RE: Updating database records (10/22/2005 18:52:30)

quote:

You would then edit the form properties to submit to a page containing your update SQL.


Duane - thank you! I believe I'm close, but am a little confused. I have two more pages now - findentry.asp which is a simple search form that gives the results on modifyentry.asp.

This is where I'm having problems. I first read that as changing the form properties on modify.asp to go to the database. But now I'm thinking I need to send it to another page for changing first?

I've been trying it one step at a time and this is where it breaks down, so I think that's where I have it wrong.

On the modifyentry.asp page (on the Spooky diet) I have the search results in a form with entries on the order of: 
<input TYPE="TEXT" NAME="Bedrooms" SIZE="40" VALUE="<%=FP_FieldHTML(fp_rs,"Bedrooms")%>">


I've tried changing that form to send to the database, but it's not working right. I've played around and made the FP_FieldHTML into FP_FieldVal, but I think somehow I'm missing a step or a page or something.

Now one thing that might be confusing me is when I've set that up to go to the database, I specify the confirmation page as entrycomplete.asp (which is also the confirmation page for the original entry.)

I think I'm not thinking straight so will step away for a little while. If you could help get me back on track, I'd be most appreciative. [sm=help.gif] (Assuming that my description here makes any sense.)

Hope ya'll are having a great weekend! It's a beautiful Autumn day here in St. Louis - sun is going down and it's just a little 'nippy' outside. Perfect weather!

Cheers!



BeTheBall -> RE: Updating database records (10/22/2005 20:20:47)

When you update records you cannot use the send to database option. That option is strictly for inserting new records. You will want to create a new page, say update.asp. On that page you insert a DRW, but in step 2 choose custom query. That is where you insert the update sql, something like:

UPDATE tableName SET (dbfield1 = '::formfield1::', etc. WHERE ID = ::ID::)

Notice the single quotes around formfield1. You use single quotes for text or memo fields. For dates, swap the single quotes for the number sign (#) and if the database field is numeric, don't put anything around ::formfield::.

You can use update.asp as a confirmation page as well. In step three of the wizard, hit more options and change the "No Records Returned" message to something like, "Update Successful!"

Does that make it any clearer?



pmagas -> RE: Updating database records (10/24/2005 17:05:29)

Yes, it makes it clearer but I continue to have problems. I get the following message:
quote:

Database Results Error
Description: Syntax error in UPDATE statement.
Number: -2147217900 (0x80040E14)
Source: Microsoft JET Database Engine

One or more form fields were empty. You should provide default values for all form fields that are used in the query.


The first page is findentry.asp with a search of MLNumber or Address - it then feeds to update-2.asp

The update-2.asp form has the following code: 
<!--#include file="_fpclass/fpdblib.inc"-->
<%
fp_sQry="UPDATE Results SET Address = '::Address::', ListPrice = ::ListPrice::, Status = '::Status::', PropType = '::PropType::', ListDate = '::ListDate::', Title = '::Title::', SubTitle = '::SubTitle::', Description = '::Description::', Bedrooms = ::Bedrooms::, Bathrooms = ::Bathrooms::, GarageSpaces = ::GarageSpaces::, Basement = '::Basement::', SqFt = ::SqFt::, Acres = '::Acres::', LotDescr = '::LotDescr::', Structure = '::Structure::', Area = '::Area::', SubArea = '::SubArea::', YrBuilt = '::YrBuilt::', ConstructStat = '::ConstructStat::', Agent = '::Agent::', Phone = '::Phone::', email = '::email::', picture = '::picture::', virtual = '::virtual::' WHERE MLNumber = '::MLNumber::'"
fp_sDefault=""
fp_sNoRecords="Update Successful!"
fp_sDataConn="entry"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&ID=3&MLNumber=202&Address=202&ListPrice=5&Status=202&PropType=202&ListDate=202&Title=202&SubTitle=202&Description=203&Bedrooms=5&Bathrooms=5&GarageSpaces=5&Basement=202&SqFt=5&Acres=202&LotDescr=202&Structure=202&Area=202&SubArea=202&YrBuilt=202&ConstructStat=202&Agent=202&Phone=202&email=202&picture=202&virtual=202&Remote_computer_name=202&User_name=202&Browser_type=202&Timestamp=135&"
fp_iDisplayCols=26
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<form METHOD="POST">
<table BORDER="0">
		<tr>
			<td><b>MLNumber:</b></td>
			<td>
			<input TYPE="TEXT" NAME="MLNumber" SIZE="40" VALUE="<%=FP_FieldVal(fp_rs,"MLNumber")%>"></td>
		</tr>
		<tr>
			<td><b>Address:</b></td>
			<td>
			<input TYPE="TEXT" NAME="Address" SIZE="40" VALUE="<%=FP_FieldVal(fp_rs,"Address")%>"></td>
		</tr>
		<tr>

---- many lines of code with fields are removed for the sake of brevity ----

<td COLSPAN="2"><br>
			<input TYPE="Submit" NAME="fp_submit"><input TYPE="Reset" NAME="fp_reset"></td>
		</tr>
	</table>
</form>
<hr>
<!--#include file="_fpclass/fpdbrgn2.inc"-->


I found a discussion here - http://www.frontpagewebmaster.com/m-169779/tm.htm and am wondering if I need to add a page in the middle of those.

thank you so much for your help!



BeTheBall -> RE: Updating database records (10/24/2005 17:13:07)

The page above needs a SELECT SQL statement to pull a record from the db and populate the form. The Update SQL should go on another page to which your form will submit.

Does that make any sense?



pmagas -> RE: Updating database records (10/24/2005 18:03:49)

By gosh, by golly - I believe it works!!! Thank you ever so much!! Now I have to make it pretty - I just hope I don't mess it up in the final versions. Time for multiple backups! [;)]

Thank you SO much!!



pmagas -> RE: Updating database records - Further Question (10/24/2005 21:39:40)

On my update.asp form (which has the results in the fields and is feeding to the form with the UPDATE command)I have a problem when there's an empty field. If I type a space in it (or a 0 for the number field), it's OK but if I leave one of the fields blank (whether it was blank before or not) I get this message:

quote:

Database Results Error
Description: Field 'Results.email' cannot be a zero-length string.
Number: -2147467259 (0x80004005)
Source: Microsoft JET Database Engine

One or more form fields were empty. You should provide default values for all form fields that are used in the query.


Thank you yet again!



Spooky -> RE: Updating database records - Further Question (10/24/2005 22:22:28)

In the design of the database - ensure that you make it not required and allow zero length for that (and pretty much all ) fields



gurusarentus -> RE: Updating database records - Further Question (12/13/2005 15:53:55)

Using asp, I'm having problems updating my database if the database is set to numeric field type and the field being updated is blank. In the database properties for numeric fields, access doesn't give a choice to allow zero length. Any suggestions?



rdouglass -> RE: Updating database records - Further Question (12/13/2005 16:14:55)

I generally will build a little function like so:

FUNCTION checkNumber(valueIn)
IF trim(valueIn&"") = "" THEN
checkNumber = 0
ELSE
checkNumber = valueIn
END IF
END FUNCTION


and then call it like:

checkNumber(Request.form("myFormField"))

That help any?



gurusarentus -> RE: Updating database records - Further Question (12/13/2005 16:30:47)

Thanks, that worked beautifully!! Any suggestions on books that I can pick up?


quote:

FUNCTION checkNumber(valueIn)
IF trim(valueIn&"") = "" THEN
checkNumber = 0
ELSE
checkNumber = valueIn
END IF
END FUNCTION



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.234375