Updating Access DB from .asp Form

	Home Register Search Help Login

Sponsors

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Search Forums

Recent Posts

Todays Posts
Most Active posts
Posts since last visit
My Recent Posts
Mark posts read

Updating Access DB from .asp Form

View related threads: (in this forum | in all forums)

Logged in as: Guest

Users viewing this topic: none

Printable Version

All Forums >> Web Development >> ASP and Database >> Updating Access DB from .asp Form

Page: [1] 2 3 next > >>


D_Sutter Posts: 48 Joined: 10/4/2002 Status: offline	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. < Message edited by D_Sutter -- 10/24/2003 1:33:51 PM >


ou812 Posts: 1566 Joined: 1/5/2002 From: San Diego Status: online	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 (in reply to D_Sutter)


D_Sutter Posts: 48 Joined: 10/4/2002 Status: offline	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. (in reply to ou812)


Long Island Lune Posts: 2340 Joined: 6/8/2002 From: New York Status: offline	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. _____________________________ (in reply to ou812)


D_Sutter Posts: 48 Joined: 10/4/2002 Status: offline	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? (in reply to Long Island Lune)


D_Sutter Posts: 48 Joined: 10/4/2002 Status: offline	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. (in reply to D_Sutter)

Long Island Lune

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

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

_____________________________

(in reply to D_Sutter)


D_Sutter Posts: 48 Joined: 10/4/2002 Status: offline	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. (in reply to Long Island Lune)


Long Island Lune Posts: 2340 Joined: 6/8/2002 From: New York Status: offline	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. _____________________________ (in reply to D_Sutter)


D_Sutter Posts: 48 Joined: 10/4/2002 Status: offline	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. < Message edited by D_Sutter -- 10/26/2003 4:11:41 PM > (in reply to Long Island Lune)


Long Island Lune Posts: 2340 Joined: 6/8/2002 From: New York Status: offline	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. < Message edited by Long Island Lune -- 10/26/2003 4:44:07 PM > _____________________________ (in reply to D_Sutter)


D_Sutter Posts: 48 Joined: 10/4/2002 Status: offline	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. (in reply to Long Island Lune)


Long Island Lune Posts: 2340 Joined: 6/8/2002 From: New York Status: offline	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. _____________________________ (in reply to D_Sutter)


Long Island Lune Posts: 2340 Joined: 6/8/2002 From: New York Status: offline	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. _____________________________ (in reply to Long Island Lune)


D_Sutter Posts: 48 Joined: 10/4/2002 Status: offline	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? (in reply to Long Island Lune)


Long Island Lune Posts: 2340 Joined: 6/8/2002 From: New York Status: offline	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. _____________________________ (in reply to D_Sutter)


D_Sutter Posts: 48 Joined: 10/4/2002 Status: offline	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. < Message edited by D_Sutter -- 10/27/2003 12:45:35 PM > (in reply to Long Island Lune)


Long Island Lune Posts: 2340 Joined: 6/8/2002 From: New York Status: offline	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. < Message edited by Long Island Lune -- 10/27/2003 1:13:54 PM > _____________________________ (in reply to D_Sutter)


D_Sutter Posts: 48 Joined: 10/4/2002 Status: offline	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) < Message edited by D_Sutter* -- 10/27/2003 1:56:51 PM > (in reply to Long Island Lune)


Long Island Lune Posts: 2340 Joined: 6/8/2002 From: New York Status: offline	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::') < Message edited by Long Island Lune -- 10/27/2003 2:17:06 PM > _____________________________ (in reply to D_Sutter)


D_Sutter Posts: 48 Joined: 10/4/2002 Status: offline	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? (in reply to Long Island Lune)


Long Island Lune Posts: 2340 Joined: 6/8/2002 From: New York Status: offline	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? _____________________________ (in reply to Long Island Lune)


Long Island Lune Posts: 2340 Joined: 6/8/2002 From: New York Status: offline	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!!! _____________________________ (in reply to Long Island Lune)


Long Island Lune Posts: 2340 Joined: 6/8/2002 From: New York Status: offline	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::') _____________________________ (in reply to Long Island Lune)


D_Sutter Posts: 48 Joined: 10/4/2002 Status: offline	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. (in reply to Long Island Lune)


Long Island Lune Posts: 2340 Joined: 6/8/2002 From: New York Status: offline	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. _____________________________ (in reply to D_Sutter)


Long Island Lune Posts: 2340 Joined: 6/8/2002 From: New York Status: offline	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. _____________________________ (in reply to Long Island Lune)


D_Sutter Posts: 48 Joined: 10/4/2002 Status: offline	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? (in reply to Long Island Lune)

Long Island Lune

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

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.

< Message edited by Long Island Lune -- 10/27/2003 10:15:10 PM >

_____________________________

(in reply to D_Sutter)


D_Sutter Posts: 48 Joined: 10/4/2002 Status: offline	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. (in reply to Long Island Lune)

Page: [1] 2 3 next > >>

All Forums >> Web Development >> ASP and Database >> Updating Access DB from .asp Form

Page: [1] 2 3 next > >>

Jump to: 1

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