Editing Records 2

[TonyU]

I'm trying to update records in an Access Db using ASP but I'm not sure how it works.

My idea is to have a page display Receipt numbers on a page pulling from the DB and when a record needs updating the user would select the receipt number and edit any field for that entry.
What's the logic behind this? I need some ideas on how to approach this task. Thanks
Tony
(-:

 

[htin11]

Hi

So the Receipt Number would be the Primary Key. So you have to use SQL in order to update.

your first page have a receipt number on the bottom, then there is a button they press, then it goes to a edit screen. the people enter the information, then post it into another page that does the updating to the database.

set cnn = Server.CreateObject("ADODB.Connection&quot

cnn.Open "whatever your database driver is"

sqltext = "UPDATE (name of table) SET (information) WHERE ReceiptNumber = ("whatever you got from previous form&quot"

cnn.Execute(sqltext);

and then redirect back to main page..

Hui

 

[TonyU]

Thanks for your quick response.
Would your suggestion work with variables? meaning, a client has changed their address and user need to updated it. User would open page displaying receipt numbers, select 1 and by selecting the 1 he could update any field needed.

Tony
(-:

 

[htin11]

hi tony

yeah you can do this

you have 3 fields, and the use just wish to updated 2

so you just have to put
a few if statement for the sqltext

sqltext = "UPDATE (name of table) SET "

if(field 1 is NOT empty)
sqltext = sqltext & "field 1 = variable1"

if ((field 1 is NOT empty) and field 2 is NOT empty)
OR (field1 is not EMPTY) and field 3 is NOTEmpty)
sqltext = sqltext & ","

if(field 2 is NOT empty)
sqltext = sqltext & "field 2 = variable2"

if (field2 is NOT empty) and field 3 is NOT empty)
sqltext = sqltext & ","

if(field 3 is NOT empty)
sqltext = sqltext & "field 3 = variable3"

sqltext = sqltext & "WHERE ReceiptNumber = "which record receiptnumber"

This will update the fields accordingly. all the variable are taken from the form previously which contain variables that the user wanted to change.

I hope this solves your question

Hui

 

[TonyU]

Man, I should've studied computers instead of Psychology. LOL

Bro, that confused the heck out of me (-:
Let's work with this example Cool? (this is how I can understand it better)

Say:
You Account = 9991584
Name = John Smith
Address1 = 123 Main Street
Address2 = Apt #115
City = Washington
State = DC
ZipCode = 20002

****** user opens site and locates your Account # **********
and needs to change your Address1,Address2,City,State & Zip
or maybe just you Apt #
Except bro, the database I'm using contains 30 fields that may or may not need updaing, I would like to give the user the ability to update whatever field needed.
- ASP Rookie








Tony
(-:

 

[Karl Blessing]

No offense TonyU , but I think your best approach (seeing the of dialogs in the past couple days of your postings) is to buy a book on ASP , and sit down to read the basics of it. It'll certainly help skim down some confusion. Karl Blessing aka kb244{fastHACK}

 

[TonyU]

I just purchase one bro, "Sams Teach Yourself ASP in 24 hours"
I hope it helps. Thanks for your advice Tony
(-:

 

[link9]

Probably an easier way would be to do this:

Get your recordset, and know what record is in question --

then, you have a form on the page with 30 text fields, each with a name, so you can assign them values with a javascript function once you have your recordset...

So, a watered down version would look like this:

<form name=updateForm method=post action=&quot;updateMe.asp&quot;>
Address1: <input type=text name=address1><br>
Address2: <input type=text name=address2><br>
City: <input type=text name=city><br>
State: <input type=text name=state><br>
Zip: <input type=text name=zip><br>
</form>

So there's your basic form, right?

Then, you need to populate the values of those elements with a little bit of vb/javascript like this:

<script language=javascript>
function populate(){
document.updateForm.address1.value=<%=rs(&quot;address1&quot%>;
document.updateForm.address2.value=<%=rs(&quot;address2&quot%>;
document.updateForm.city.value=<%=rs(&quot;city&quot%>;
document.updateForm.state.value=<%=rs(&quot;state&quot%>;
document.updateForm.zip.value=<%=rs(&quot;zip&quot%>;
}
</script>

The above function assumes that you have a recordset called 'rs' that has the cursor on the row that you are wanting to update -- all it's doing is outputting the current values to the text boxes so that the user can see what's currently in there --

Then, you need to call the javascript function in your body tag for the onLoad event:

<body onLoad=&quot;populate();&quot;>

And there you have it -- Step 1 complete --

You would then provide them a button at the bottom that would submit the form, sending its contents to updateMe.asp, and there you could just do this:

Again, I'm assuming here that you have the recordset open and the cursor moved to the proper location in that recordset... you could pass the pk identifier between the two pages via a hidden form field if you wanted to do that, and then open the recordset straight to the proper record.

dim address1, address2, city, state, zip
address1 = request.form(&quot;address1&quot
address2 = request.form(&quot;address2&quot
city = request.form(&quot;city&quot
state = request.form(&quot;state&quot
zip = request.form(&quot;zip&quot

rs(&quot;address1&quot = address1
rs(&quot;address2&quot = address2
rs(&quot;city&quot = city
rs(&quot;state&quot = state
rs(&quot;zip&quot = zip
rs.update

There ya go -- table updated, and task complete. Make sure that you have selected the proper lockType and cursorType for your recordset so that it is updateable (otherwise you will get an error when you try to .update). There is a FAQ in the ASP FAQ section here at this site that explains all the ins and outs about those recordset properties, so if you don't know what they are, it would be a good read.

good luck!

just as an aside, I wasn't trying to contradict htin11 up there, because what he said was absolutely correct, but he was using SQL statements to achieve the update, whereas I think it's easier for someone who isn't so experienced with SQL to update a table using recordsets, and then updating the tables from there. Just makes a little more sense, although the SQL way is more efficient. ;-)

 

[TonyU]

Now, that made more sense.
Not that htin11 explanation was unclear but I found Link9's to be in plain English (you know what I mean)
Thanks again fellas I appreciate it as usual and let me get back to reading my book. Tony
(-:

 

[TonyU]

Shhh, I'm trying to read fellas. LOL Only if I really helped you
|
|
|
|
V (-:

 

[Karl Blessing]

hehe, well least you seem to understand Access (or database) which is good, Also I Can speak in plain english, or I Can speak in Tek-Speak, around here, I'm more used to Tek-speak, when speaking to someone in person that I do not know well, I lean alot more towards english, being a developer, communication skills really pay off big time, especially if you have to answer to someone who is non-technical, when there is an issue. (future advise #-) dont blind people, to buy yourself time, it'll only come back to bite you) Karl Blessing aka kb244{fastHACK}