Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    USA
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Editing/Updating Access Database via ASP

    I have an Access database that I'm trying to edit/update via VBScript via 3 different ASP files.
    My problem is that although the scripts all seem to have the proper syntax, one works all of the time, one works for only some records, and one never works.

    Can anyone see any obvious errors that I'm making? Tech support at the web hosting company has tried, but can't discover any problems.

    I don't think that it's a "rights" problem because one of the files edits the database.

    updatepassword.asp allows members to change their login password. This file always works.
    <% Language=VBScript %>
    <% Response.Buffer = True %>

    <HTML>

    <BODY>

    <%
    Dim SqlStmt
    Dim rst
    Dim c

    Set dbConn = Server.CreateObject("ADODB.Connection")
    dbConn.Open Application("Members_ConnectionString")

    If Session("vIDOK") = True then
    SqlStmt="SELECT * FROM tMembers "
    SqlStmt=SqlStmt & "WHERE LoginID = '" & Session("vID") & "' "
    Set rst = Server.CreateObject("ADODB.Recordset")
    rst.Open SqlStmt,dbconn,adOpenKeyset,adLockOptimistic

    rst.MoveFirst
    If (rst.BOF and rst.EOF) then
    response.redirect "/memberloginfail.htm"
    else
    rst("LoginPwd")=Request.Form("NewPwd")
    rst("LastChangeDate")=Date
    rst("LastChangeBy")=Session("vID")
    rst.Update
    response.redirect "/_scripts/memberhomepage.asp"
    End if
    Else
    response.redirect "/memberlogin.htm"
    End If

    rst.close
    set rst = Nothing

    dbConn.Close
    Set dbConn = Nothing

    </BODY>

    </HTML>

    updatemyrecord.asp allows members to change certain fields, such as phone number, address, etc. I haven't been able to see any pattern as to why this file allows some records to be updated, but not others.
    <% Language=VBScript %>
    <% Response.Buffer = True %>
    <HTML>
    <BODY>

    <%
    Dim SqlStmt
    Dim rst
    Dim fld

    Set dbConn = Server.CreateObject("ADODB.Connection")
    dbConn.Open Application("Members_ConnectionString")

    SqlStmt="SELECT * from tMembers WHERE LoginID = '" & Session("vID") & "'"
    Set rst = Server.CreateObject("ADODB.Recordset")
    rst.Open SqlStmt,dbconn,adOpenKeyset,adLockOptimistic

    rst("FirstName")=request.form("FirstName")
    rst("MiddleInitial")=request.form("MiddleInitial")
    rst("LastName")=request.form("LastName")
    rst("EmailAddress")=request.form("EmailAddress")
    If request.form("MailWhere")="W" then
    rst("MailToHomeAddress")=False
    Else
    rst("MailToHomeAddress")=True
    end if
    rst("WorkCompany")=request.form("WorkCompany")
    rst("WorkAddress")=request.form("WorkAddress")
    rst("WorkCity")=request.form("WorkCity")
    rst("WorkState")=request.form("WorkState")
    rst("WorkPostalCode")=request.form("WorkPostalCode ")
    rst("WorkPhone")=request.form("WorkPhone")
    rst("WorkFaxNumber")=request.form("WorkFaxNumber")
    rst("Extension")=request.form("Extension")
    rst("HomeAddress")=request.form("HomeAddress")
    rst("HomeCity")=request.form("HomeCity")
    rst("HomeState")=request.form("HomeState")
    rst("HomePostalCode")=request.form("HomePostalCode ")
    rst("HomePhone")=request.form("HomePhone")
    If request.form("FE")="Yes" then
    rst("FE")=True
    Else
    rst("FE")=False
    end if
    If request.form("PR")="Yes" then
    rst("PR")=True
    Else
    rst("PR")=False
    end if
    If request.form("SI")="Yes" then
    rst("SI")=True
    Else
    rst("SI")=False
    end if
    If request.form("SU")="Yes" then
    rst("SU")=True
    Else
    rst("SU")=False
    end if
    If request.form("FO")="Yes" then
    rst("FO")=True
    Else
    rst("FO")=False
    end if
    If request.form("AD")="Yes" then
    rst("AD")=True
    Else
    rst("AD")=False
    end if
    If request.form("NT")="Yes" then
    rst("NT")=True
    Else
    rst("NT")=False
    end if
    If request.form("CO")="Yes" then
    rst("CO")=True
    Else
    rst("CO")=False
    end if
    rst.Update

    rst.close
    set rst = Nothing

    dbConn.Close
    Set dbConn = Nothing

    %>

    </BODY>
    </HTML>

    enternewmember.asp lets the membership chairperson add new members to the database. This file doesn't generate any error messages, but new records aren't being added.
    <% Language=VBScript %>
    <% Response.Buffer = True %>
    <HTML>
    <BODY>

    <%
    Dim SqlStmt
    Dim rst
    Dim fld
    Dim Maxnum
    Dim NewID
    Dim NewPwd
    Dim RandomNum
    Dim CurMinute

    Set dbConn = Server.CreateObject("ADODB.Connection")
    dbConn.Open Application("Members_ConnectionString")

    SqlStmt="SELECT Max(MemberID) AS lastid FROM tMembers"
    Set rst = Server.CreateObject("ADODB.Recordset")
    rst.Open SqlStmt,dbconn

    Maxnum = rst("lastid").value
    Maxnum = cstr(Maxnum+1)
    NewID = "ABC" & Maxnum
    rst.Close
    set rst = Nothing

    randomize
    RandomNum = cstr(int(rnd * 999))
    RandomNum = Right("000" & RandomNum,3)
    CurMinute = minute(now)
    If CurMinute <=29 then
    NewPwd = Ucase(left(request.form("FirstName"),2)) & Lcase(right(request.form("LastName"),2)) & RandomNum
    Else
    NewPwd = Ucase(left(request.form("LastName"),2)) & RandomNum & Lcase(right(request.form("FirstName"),2))
    End if

    Set rst = Server.CreateObject("ADODB.Recordset")
    rst.Open "tMembers",dbconn,adOpenKeyset,adLockOptimistic,ad CmdTable
    rst.AddNew

    rst("FirstName")=request.form("FirstName")
    rst("MiddleInitial")=request.form("MiddleInitial")
    rst("LastName")=request.form("LastName")
    rst("HomeAddress")=request.form("HomeAddress")
    rst("HomeCity")=request.form("HomeCity")
    rst("HomeState")=request.form("HomeState")
    rst("HomePostalCode")=request.form("HomePostalCode ")
    rst("HomePhone")=request.form("HomePhone")
    rst("WorkCompany")=request.form("WorkCompany")
    rst("WorkAddress")=request.form("WorkAddress")
    rst("WorkCity")=request.form("WorkCity")
    rst("WorkState")=request.form("WorkState")
    rst("WorkPostalCode")=request.form("WorkPostalCode ")
    rst("WorkPhone")=request.form("WorkPhone")
    rst("Extension")=request.form("Extension")
    rst("WorkFaxNumber")=request.form("WorkFaxNumber")
    rst("EmailAddress")=request.form("EmailAddress")
    If request.form("MailWhere")="W" then
    rst("MailToHomeAddress")=False
    Else
    rst("MailToHomeAddress")=True
    end if
    rst("JoinedYear")=request.form("JoinedYear")
    rst("Gender")=request.form("Gender")
    rst("DuesClass")=request.form("DuesClass")
    rst("AddComments")=request.form("AddComments")
    If request.form("FE")="Yes" then
    rst("FE")=True
    Else
    rst("FE")=False
    end if
    If request.form("SI")="Yes" then
    rst("SI")=True
    Else
    rst("SI")=False
    end if
    If request.form("PR")="Yes" then
    rst("PR")=True
    Else
    rst("PR")=False
    end if
    If request.form("SU")="Yes" then
    rst("SU")=True
    Else
    rst("SU")=False
    end if
    If request.form("FO")="Yes" then
    rst("FO")=True
    Else
    rst("FO")=False
    end if
    If request.form("OT")="Yes" then
    rst("OT")=True
    Else
    rst("OT")=False
    end if
    If request.form("AD")="Yes" then
    rst("AD")=True
    Else
    rst("AD")=False
    end if
    If request.form("CO")="Yes" then
    rst("CO")=True
    Else
    rst("CO")=False
    end if
    If request.form("NT")="Yes" then
    rst("NT")=True
    Else
    rst("NT")=False
    end if
    rst("Active")=True
    rst("Deceased")=False
    rst("AdminRights")=False
    rst("LoginID")=NewID
    rst("LoginPwd")=NewPwd
    rst("LastChangeDate")=Date
    rst("LastChangeBy")=Session("vID")

    rst.Update
    rst.MoveFirst

    rst.close
    set rst = Nothing

    dbConn.Close
    Set dbConn = Nothing

    Response.write "You've added the following member:
    "
    Response.write "First Name: " & request.form("FirstName") & "
    "
    Response.write "Last Name: " & request.form("LastName") & "
    "
    Response.write "
    "
    Response.write "Please contact them with the following information:
    "
    Response.write "Login ID: " & NewID & "
    "
    Response.write "Login Password: " & NewPwd & "
    "
    Response.write "

    "
    %>



    </BODY>
    </HTML>

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Editing/Updating Access Database via ASP

    Does it make any difference if you change the CursorType from adOpenKeyset to adOpenDynamic? It may sound as though this would retrieve a boatload of records, but if you modulate the CacheSize property of the recordset, you probably can tune the performance sufficiently.

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Editing/Updating Access Database via ASP

    A couple of thoughts...

    First - everything looks like well-written code. Kudos!

    Second - I see a couple of ADO issues that seem like they may be the problem (based on my limited, but ever-increasing experience).

    Basically, I believe you have to tell ADO to move to a certain record before you can edit a record - even if the recordset only returns 1 record...

    So, in your "updatemyrecord.asp" file, you'll want to do a rst.MoveFirst right after your rst.Open.... line (before updating your records).

    Also, I usually don't use the adCmdTable option in my rst.Open command. I don't know if that has anything to do with it or not... I think the adCmdTable is meant to be used with SQL Server rather than Access, but I guess it wouldn't hurt to try...

    I hope this helps

  4. #4
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    USA
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Editing/Updating Access Database via ASP

    I 'think' that I've narrowed the problem down to Null values on the input form.

    Although many of the database fields are not required, if I leave some of them blank on the form, the record isn't updated. However, if I enter something into each field, I can update the record.

    Does this make sense?

    The current design of the database does not allow Zero Length Strings. Should I change this field setting?

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Editing/Updating Access Database via ASP

    Yes - that can be an issue. I'm not sure what other developers do with this, but I usually set the Allow Zero Length Strings to True (even though the default is False). This makes things flow a bit easier when working with ADO/ASP.

    Of course, that all depends on your data validation needs. Leaving Allow Zero Length set to True is only recommended if you have either a non-required field or some sort of validation technique in place (either client or server side).

    Also, I don't recall whether you've alred mentioned this in your previous posts, but you may want to try adding a few lines that help you isolate errors: Include a "On Error Resume Next" line before starting your recordset operaions (such as inserting or updating data) and a "If cnn.Errors.count > 0 Then... " block after your recordset oprations. I usually dump all errors into a string that I always show in my document. If there are no errors, the string is invisible, otherwise, the user sees an error message with th error and description of the problem.

    Hope this helps!

  6. #6
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    USA
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Editing/Updating Access Database via ASP

    Changing the Allow Zero Length Strings setting to True seems to have corrected my updating problems.

    Thanks,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •