Updating Access DB from .asp Form (Full Version)

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



Message

D_Sutter -> Updating Access DB from .asp Form (10/24/2003 10:22:16)

This is probably something simple, but I have looked and looked and can't find the information I need.

The 1st .asp page is inputting the username and password and it retrieves the record and puts it into fields (form) on the 2nd .asp page. This works fine. I have the form set to "send to database" and have mapped all the input fields to the database columns.

The problem occurs when I change the information on the 2nd .asp page and hit the update(submit) button, it looks like everything goes fine. I get my confirmation page. If I go back into the record (the same way as before), the information was not updated.

Is there some secret how to get it to update?

Any help would be appreciated.



ou812 -> RE: Updating Access DB from .asp Form (10/24/2003 13:54:51)

It sounds like you are not really using the fields you are filling out. Can you supply the code for these areas for us to look at(I guess that would be the 2nd asp page)?

-brian



D_Sutter -> RE: Updating Access DB from .asp Form (10/24/2003 14:14:49)

Not sure which code you need to see, but here is the code that was generated by fp to handle the database update.

<%
' FP_ASP ASP Automatically generated by a FrontPage Component. Do not Edit.

On Error Resume Next
Session("FP_OldCodePage") = Session.CodePage
Session("FP_OldLCID") = Session.LCID
Session.CodePage = 1252
Err.Clear

strErrorUrl = "Error.asp"

If Request.ServerVariables("REQUEST_METHOD") = "POST" Then
If Request.Form("VTI-GROUP") = "0" Then
Err.Clear

Set fp_conn = Server.CreateObject("ADODB.Connection")
FP_DumpError strErrorUrl, "Cannot create connection"

Set fp_rs = Server.CreateObject("ADODB.Recordset")
FP_DumpError strErrorUrl, "Cannot create record set"

fp_conn.Open Application("DriverDBX_ConnectionString")
FP_DumpError strErrorUrl, "Cannot open database"

fp_rs.Open "DrvTable", fp_conn, 1, 3, 2 ' adOpenKeySet, adLockOptimistic, adCmdTable
FP_DumpError strErrorUrl, "Cannot open record set"

fp_rs.AddNew
FP_DumpError strErrorUrl, "Cannot add new record set to the database"
Dim arFormFields0(5)
Dim arFormDBFields0(5)
Dim arFormValues0(5)

arFormFields0(0) = "iDrvName"
arFormDBFields0(0) = "DrvName"
arFormValues0(0) = Request("iDrvName")
arFormFields0(1) = "iDrvBio"
arFormDBFields0(1) = "DrvBio"
arFormValues0(1) = Request("iDrvBio")
arFormFields0(2) = "iDrvAward"
arFormDBFields0(2) = "DrvAward"
arFormValues0(2) = Request("iDrvAward")
arFormFields0(3) = "iDrvPic"
arFormDBFields0(3) = "DrvPic"
arFormValues0(3) = Request("iDrvPic")
arFormFields0(4) = "iDrvPswd"
arFormDBFields0(4) = "DrvPswd"
arFormValues0(4) = Request("iDrvPswd")

FP_SaveFormFields fp_rs, arFormFields0, arFormDBFields0

If Request.ServerVariables("REMOTE_HOST") <> "" Then
FP_SaveFieldToDB fp_rs, Request.ServerVariables("REMOTE_HOST"), "Remote_computer_name"
End If
If Request.ServerVariables("HTTP_USER_AGENT") <> "" Then
FP_SaveFieldToDB fp_rs, Request.ServerVariables("HTTP_USER_AGENT"), "Browser_type"
End If
FP_SaveFieldToDB fp_rs, Now, "Timestamp"
If Request.ServerVariables("REMOTE_USER") <> "" Then
FP_SaveFieldToDB fp_rs, Request.ServerVariables("REMOTE_USER"), "User_name"
End If

fp_rs.Update
FP_DumpError strErrorUrl, "Cannot update the database"

fp_rs.Close
fp_conn.Close

Session("FP_SavedFields")=arFormFields0
Session("FP_SavedValues")=arFormValues0
Session.CodePage = Session("FP_OldCodePage")
Session.LCID = Session("FP_OldLCID")
Response.Redirect "Confirm.asp"

End If
End If

Session.CodePage = Session("FP_OldCodePage")
Session.LCID = Session("FP_OldLCID")

%>

The form field names are just the database fields names with an "i" infront of them. When I did not have the confirm.asp page setup, it showed me the values that were going to be updated and they looked correct.

Thanks.



Long Island Lune -> RE: Updating Access DB from .asp Form (10/24/2003 14:15:12)

You are using an UPDATE query and not an INSERT query right? Just checking... [;)]

It sounds like your using an INSERT query on page 2 to input the info, then when you try to change the info your using the same INSERT query? You would need to use an UPDATE query to alter the info.

If I understood what you were saying.



D_Sutter -> RE: Updating Access DB from .asp Form (10/24/2003 14:35:10)

I am using an input query to populate the form fields when the page is loaded. I want to take the new information from the form fields and update the database.

Do I need to add something else to do this?



D_Sutter -> RE: Updating Access DB from .asp Form (10/25/2003 15:43:53)

I am still having trouble with this...can anyone help me?

Thanks.



Long Island Lune -> RE: Updating Access DB from .asp Form (10/25/2003 18:31:59)

quote:

I am using an input query to populate the form fields when the page is loaded
I looked your code over and do not see a query.

This is how I would handle a situation like yours:
1): Page two loads the data from your base into a form.
2): In this form you can change any info in the fields that you wish.
3): Once the user is done making his/her changes, they press an UPDATE button and the form's info is sent to page three.
4): Page three updates the database.

It looks to me that everything is done on a single page in your code? I cannot assist you in that. Or with Confirmation Page's generated inside FP.

The first part of your system works.
quote:

The 1st .asp page is inputting the username and password and it retrieves the record and puts it into fields (form) on the 2nd .asp page. This works fine.
That's great. Your 1/2 way successfully done.

So what I would do is create a page 3. Post the data from your form on page 2 to page 3. Take your asp UPDATE code (only) out of page 2 and place it in page 3.

Try this and see what results you get. If it still does not work post here again. There's a much easier way to UPDATE a existing database record using the DRW (straight or dieted).



D_Sutter -> RE: Updating Access DB from .asp Form (10/26/2003 9:24:02)

I am not the best at this, so if you have a better (easier) way to update the database, I would like to hear it.

Thanks.



Long Island Lune -> RE: Updating Access DB from .asp Form (10/26/2003 15:06:38)

Are you familar with creating DRW's?
If not I can step you through that.



D_Sutter -> RE: Updating Access DB from .asp Form (10/26/2003 16:03:44)

You better treat me as a novice...

I will not be affended if you give me too much information.

I believe I use that to retrieve the information from the database and put it into the form fields on my 2nd page. This part works fine. It just doesn't want to update the database from there.

I was not real sure how to just cut the update part that was in page 2 and put it into page 3. The update part was generated by Frontpage when I told it to send the information to a database. When I change it to send it to another page, that code disappears. Does page 3 need to be as .asp page as well? When I put the code on that page and hit save, it would not save just that code on the page.

Thanks.



Long Island Lune -> RE: Updating Access DB from .asp Form (10/26/2003 16:43:39)

OK, page 1 and page 2 seem fine. If you go the DRW route, you will not need your UPDATE CODE on page 2 anymore. It will be done on page 3 by a DRW.

The only thing you need to do for page 2 is make sure that your visitors are able to change their data. And you have that done already.

So here's what you should do for now:

1): On page 2 - POST the data from that form to page 3. That's all you have to do on page 2.
2): I need all the field names that you wish to be updatable:

Were these all of them?:
iDrvName, iDrvBio, iDrvAward, DrvAward, iDrvPic, iDrvPswd

I assume all of these are data type "Text" fields? If not, clarify what each one is.

3): What is the name of the TABLE in your Database you want UPDATABLE?

That's all I need now.
I'll walk you through your first DRW.
It's very easy.
[:)]



D_Sutter -> RE: Updating Access DB from .asp Form (10/26/2003 16:53:31)

When you say post the form result to the page 3 (.asp), do I do that where I had originally set it to "send to database"? Under form properties, do I choose "send to other"?

Database connection - DriverDBX
Table Name - DrvTable

form fields Type database fields
iDrvNum Text DrvNum
iDrvPswd Text DrvPswd
iDrvName Text DrvName
iDrvBio Memo DrvBio
iDrvAward Memo DrvAward
iDrvPic Text DrvPic

Thanks.



Long Island Lune -> RE: Updating Access DB from .asp Form (10/26/2003 17:15:45)

Yes.

Here's the complete instructions:

1): Click on your form.
2): Right-click and select "Form Properties".
3): Instead of "Send To Database", click on "Send To Other"
4): Then click on the "Options" button on the bottom.
5): In the "Action" field, type the addess to page 3.
6): Make sure the "Method" field says "Post".
7): Click "OK".
8): Click on "OK" again.

Now the form on page 2 is posting the data to page 3.

Verify that the operations I layed out ABOVE are completed.
I'll start working on your query.



Long Island Lune -> RE: Updating Access DB from .asp Form (10/26/2003 18:54:28)

Opps... forgot one thing:

Is you database connected to your FP project? In other words, did you either:

a): create the datatabase inside FP letting FP create it for you?
Or...
b): Import the database into your project?

If you didn't:

1): Load FP.
2): Load your site / project.
3): Click "NEW" from the "File" menu.
4): Select "Import..." from the "File" menu.
5): Click "Add File" from the "Import" dialog box.
6): Locate your actual database file (not the DSL) and click "Open".
7): Click "OK" at the bottom.

The system will take a few moments to import the datatabse in. Once done a small dialog box will appear.

8): In the small dialog box, give your database a reference name. This will be the name that you reference this database as - inside FP.
9): Click "Yes".

Wait while it completes the importing task.

Then a dialog box will appear telling you that you should save this database in the /fpdb directory. Cick "Yes" to save it there.

Wait while it completes the importing task. Once complete, the dialog box will close. Now you have to verify the connection.


10): Select "Web Settings" from the "Tools" menu.
11): Inside the "Web Settings" dialog box, select "Database".
12): Highlight your database from the list.
13): Click "Verify".

It might take a moment or two...

14): Click "Apply".
15): Click "OK".

That's it.
Your database is now connected to your project and you can create DRW's to your heart desire.

If you datatabse was already imported or connected to your project, then disregard this entire instruction post.

Verify that you datatabse is connected to your project.
And...
Verify my "POSTING from page 2 to page 3" instructions too.

[:)]



D_Sutter -> RE: Updating Access DB from .asp Form (10/27/2003 6:06:15)

I have the database connected already.

I have the page 2 posting to page 3. What do have page 3 doing? I inserted a DRW on page 3 and it just displays the information already in the database, not the changed information from page 2.

How do I get the form input to be put into the database fields?



Long Island Lune -> RE: Updating Access DB from .asp Form (10/27/2003 12:28:54)

OK, looking good. I'm glad you created the DRW but that was all it was supposed to do - display.

We are going to change one thing in the DRW to make it updatable. But before we move on I finished your query:

UPDATE DrvTable Set (DrvNum= ‘::DrvNum’, DrvPswd= ‘::DrvPswd::’, DrvPswd= ‘::DrvPswd::’, DrvBio= ‘::DrvBio::’, DrvAward= ‘::DrvAward::’, DrvPic= ‘::DrvPic::’ WHERE ( ??????? )

I have one question:
We still need a field value for the query that we can use to distinquish the record you want to UPDATE from all the others in your database.

Example:
WHERE (ID =

What field do you want to use to complete your query so the system knows which record to update???

Once we have this little piece of info, then we can complete your DRW UPDATE. Your 95% there.



D_Sutter -> RE: Updating Access DB from .asp Form (10/27/2003 12:44:41)

The database keys are DrvNum & DrvPswd. They both have to match before you record should be allowed to update.

Where does the above code go?

Thanks.



Long Island Lune -> RE: Updating Access DB from .asp Form (10/27/2003 13:13:39)

You mean like this;???

UPDATE DrvTable Set (DrvNum= ‘::DrvNum’, DrvPswd= ‘::DrvPswd::’, DrvPswd= ‘::DrvPswd::’, DrvBio= ‘::DrvBio::’, DrvAward= ‘::DrvAward::’, DrvPic= ‘::DrvPic::’ WHERE ('::DrvNum::' = '::DrvPswd::')

That doesn't look right to me. Don't know if that will work. Your going to have to work out your own WHERE ( ??? ) to make if fit your table fields. Most times it's a simple numeric type that is connected to the ID field of the record. Example: WHERE (ID = ::ID::).

Where to put it:

1): Double-click your yellow Database Region on your page.
2): Click "Next" once. This will put you on page "Step 2 of 5".
3): Click "Custom Query".
4): Click "Edit" beneath "Custom Query".
5): Copy the query I provided you above.
6): Insert it in place of the query showing in the dialog box.
7): Click "Verify Query".
8): Click "OK".
9): Click "Next" until you get to page "Step 4 of 5".
10): Select "Table - one record per row".
11): Make sure NONE of the checkboxes are checked beneath.
12): Click "Next".
13): On page "Step 5 of 5", click "Display all records together".
14): On page "Step 5 of 5", unclick "Add search form".
15): Click "Finish".

Save page, upload and test.
[:)]



D_Sutter -> RE: Updating Access DB from .asp Form (10/27/2003 13:54:32)

I entered the information like you said and I am getting this error message. Did you mean to have 2 password sections in the query line? I also noticed that some :: looked like they were missing in the DrvNum part of it. The information coming from the form have the same names as the DB fields except they start with "i" (ie. DrvNum = iDrvNum). The DrvNum fields is the only field I need to match before update.

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

UPDATE DrvTable Set (DrvNum= ‘1DrvPswd2DrvPswd3DrvBio4DrvAward5DrvPic6DrvNum7DrvPswd')

against a database using the connection string

DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=fpdb/DriverX.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] Syntax error in UPDATE statement.

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



Long Island Lune -> RE: Updating Access DB from .asp Form (10/27/2003 14:16:33)

Yep. I placed two DrvPswd's in by mistake. I adjusted the WHERE ( ??? ) too:

UPDATE DrvTable Set (DrvNum= ‘::DrvNum::’, DrvPswd= ‘::DrvPswd::’, DrvBio= ‘::DrvBio::’, DrvAward= ‘::DrvAward::’, DrvPic= ‘::DrvPic::’ WHERE (DrvNum' = '::DrvNum::' And DrvPswd = '::DrvPswd::')



D_Sutter -> RE: Updating Access DB from .asp Form (10/27/2003 14:55:18)

I have it all in place and when I test it, I get...

Database Results Wizard Error
The operation failed. If this continues, please contact your server administrator.

Do you happen to know what this is from?

On page 2, in the text box properties (Name), I have called the fields iDrvNum, iDrvPswd... Do I need to use those field names in the update statement?



Long Island Lune -> RE: Updating Access DB from .asp Form (10/27/2003 14:56:28)

Question:

In one of my emails above, I told you to do this:

4): Click "Edit" beneath "Custom Query".
5): Copy the query I provided you above.
6): Insert it in place of the query showing in the dialog box.
7): Click "Verify Query".

When you clicked the "Verify" button, I'm assuming it verify correctly?



Long Island Lune -> RE: Updating Access DB from .asp Form (10/27/2003 14:58:41)

quote:

On page 2, in the text box properties (Name), I have called the fields iDrvNum, iDrvPswd... Do I need to use those field names in the update statement?

YES!!!

Change all the fields that are in my query to the names that are in the form.
That makes a BIG difference!!! [;)]



Long Island Lune -> RE: Updating Access DB from .asp Form (10/27/2003 15:00:31)

Something like:

UPDATE DrvTable Set (DrvNum= ‘::iDrvNum::’, DrvPswd= ‘::iDrvPswd::’, DrvBio= ‘::iDrvBio::’, DrvAward= ‘::iDrvAward::’, DrvPic= ‘::iDrvPic::’ WHERE (DrvNum' = '::iDrvNum::' And DrvPswd = '::iDrvPswd::')



D_Sutter -> RE: Updating Access DB from .asp Form (10/27/2003 15:04:30)

I thought that might be the case, so I had tried that already. I still cannot get it to update...getting same message as posted above. Do I need to have every fields in my database listed here, or can I update some?

Thank you for all your time on this...it can be very frustrating.



Long Island Lune -> RE: Updating Access DB from .asp Form (10/27/2003 15:12:28)

No, you do not have to list all the fields.
Only the ones you want to update.

Is there another way you can locate the record you want updated? Like sticking the tables unique numeric ID in the form, then passing it to page 3. THAT's WHERE I think the problem is. In the WHERE clause.

Everything else is correct. [;)]



Long Island Lune -> RE: Updating Access DB from .asp Form (10/27/2003 15:16:07)

DrvNum
DrvPswd
DrvBio
DrvAward
DrvPic

These are ALL correct in your table? No miss-spelling or anything?
What is the name of your autofield ID? Try that in the WHERE clause.

I have this scenario on at least 50 different DRW's across 11 sites and it works perfectly everytime. The only dfifference is that I use my autonumber ID field in the WHERE clause.



D_Sutter -> RE: Updating Access DB from .asp Form (10/27/2003 15:25:56)

The fields are...

DrvNum
DrvPswd
DrvName
DrvBio
DrvAward
DrvPic

The DrvNum is the way I tell that I am updating the correct record. If I were using the autofield ID, how would I know when I was in the correct record?



Long Island Lune -> RE: Updating Access DB from .asp Form (10/27/2003 20:37:48)

quote:

If I were using the autofield ID, how would I know when I was in the correct record?

Well, you would have to have pulled it out of the database on page 1 with the other fields and had it loaded in your form in a hidden field.

quote:

The DrvNum is the way I tell that I am updating the correct record.

Why are you using two fields in the WHERE clause?

I found an error in my statement...

Lets try this:
UPDATE DrvTable Set (DrvNum= ‘::DrvNum::’, DrvPswd= ‘::DrvPswd::’, DrvBio= ‘::DrvBio::’, DrvAward= ‘::DrvAward::’, DrvPic= ‘::DrvPic::’ WHERE (DrvNum = '::DrvNum::')

This should be able to pass the VERIFY test in the DRW Wizard.



D_Sutter -> RE: Updating Access DB from .asp Form (10/28/2003 6:04:31)

I went back and recreated my database and used the auto number. The database fields are...

Table = DrvTable
ID
DrvNum
DrvPswd
DrvName
DrvBio
DrvAward
DrvPic

Form fields are called same, except "i" in front of them.

Page 3 has the following statement...
UPDATE DrvTable
SET DrvPswd=('::iDrvPswd::'),
DrvName=('::iDrvName::'),
DrvBio=('::iDrvBio::'),
DrvAward=(::iDrvAward::),
DrvPic=('::iDrvPic::')
WHERE ID=('::iID::')

I still get the same error message.
Database Results Wizard Error
The operation failed. If this continues, please contact your server administrator.



Page: [1] 2 3   next >   >>

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.125