• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

ASP update Access Record

I'm stuck.  Trying to update a Access Database with the following code.  No errors are reported, and no records are updated.  Can you see anything wrong here?


Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConn
strNewPassword=request.form("NewPassword")
sql="UPDATE MEMBERS SET Password = '" & strNewPassword & "' where memberID = " & memberID
on error resume next
objConn.Execute sql, RecordsAffected
response.write err.message
response.write RecordsAffected
0
rrhandle8
Asked:
rrhandle8
  • 4
  • 3
1 Solution
 
Robert SchuttSoftware EngineerCommented:
You don't see the error because you use "on error resume next".

Use Err.Number and Err.Description to see what's wrong (not Err.message which is probably generating an error in itself).
0
 
rrhandle8Author Commented:
I did as you suggested, but nothing gets written out.

            Set objConn = Server.CreateObject("ADODB.Connection")
            objConn.Open strConn
            strNewPassword=request.form("NewPassword")
            sql="UPDATE MEMBERS SET Password = '" & strNewPassword & "'    
                                   where memberID = " & memberID
            response.write sql

            objConn.Execute sql, RecordsAffected
            response.write err.number, err.description
            response.write RecordsAffected
            response.write "end"
0
 
Robert SchuttSoftware EngineerCommented:
Ok, I fear something basic is going wrong here. We may have to go back to square one.

Please specify how you are calling this code. You posted this in Topic Area 'SQL Query Syntax' but I'm not sure the problem is actually in that area. The code indicates you're using Classic ASP or VB script, or...?

Please be a bit more specific. Try to write out something earlier, make sure you're actually executing the code because as it is, "nothing is written out" means I can't be sure what problem we're trying to solve.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
rrhandle8Author Commented:
Classic ASP and a .mdb database.  The code is executing just fine. When the page first shows, it asks for a username and password, then for a new pasword.  When the submit button is clicked, it returns all the information for this user, and prints it to the screen, unless some invalid information was entered.  In that case, the appropriate error message is returned to the user.

I had also put a response.write before and after the "objConn.Execute sql", and both lines wrote something to the screen.

An expert on another forum put it mildly: "The Execute method is buggy as hell.  Never use it."

So, I rewrote the code to use the Update method of the recordset, and everything worked.

Sure would like to know what is wrong with the Execute method.  I see examples all over the internet, and I use it frequently when working with SQL Server.  Do you know the problem?  Do you agree with the other expert?
0
 
Robert SchuttSoftware EngineerCommented:
I have used it extensively in Classic ASP and never encountered problems. There could be a problem with certain providers but if anything is 'buggy as hell' I would hope updating to the latest version would fix that. For Access (and more) there is a new driver, don't know if you're using that, it's the ACE driver, maybe that other expert was talking specifically about JET?

I can't imagine he meant ADO in general. I mainly use MSSQL, less frequently MS Access but never seen problems with statements like this on that level.

One problem, depending on the possible contents of strNewPassword would of course be if there could be special characters but if the contents are checked beforehand (and also that memberID is numeric) then I don't see a problem.
0
 
rrhandle8Author Commented:
I am going to create a special page to test this out.  I find it hard to believe something would be "buggy as hell" especially after getting your reply.

ACE drive.  That is new to me.  I'll have to do some homework on that.  Will get back to you soon.
0
 
Robert SchuttSoftware EngineerCommented:
ACE drive.  That is new to me
I only heard about it relatively recently myself, have mentioned it in a few questions since then, especially for 64 bit systems as there have been problems with JET for that. I downloaded it from here: http://www.microsoft.com/en-us/download/details.aspx?id=13255
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now