SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist OwainGDWilliams's Avatar
    Join Date
    Aug 2005
    Location
    Scotland, UK
    Posts
    435
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ASP Access Connections.

    I wasn't sure if this should be under ASP or Databases but since its built in to an ASP site I have gone for here......


    My problem is I am trying to create a very simple data entry site e.g. Guestbook (teaching myself ASP). I have managed to get a site to add, remove and update details in an Access database which had one table without a problem. Now I have 1 database and 2 tables.

    The first table looks like this :

    tblComments
    > ID_no (PK)
    > Comments
    > UserID

    The Second looks liket his :
    tblUserList
    > UserID (PK)
    > userName
    > userPassword
    > userFullName

    What I want is when a user adds a comment his/her name is posted under their post, I have managed to create a log in screen and each User has a unique UserID number. So when someone posts a comment, its labeled with their UserID number which then should display the name attached to that UserID.

    My problem just now is when I enter in a comment and click submit I get the following error :
    Code:
    Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
    [Microsoft][ODBC Microsoft Access Driver] Cannot update. Database or object is read-only.
    /guestbook/guestbook.asp, line 36
    Here is the code below from the Guestbook.asp page which should update the database:
    Code:
    <&#37;
    'Dimension variables
    Dim adoCon              'Holds the Database Connection Object
    Dim rsAddComments   'Holds the recordset for the new record to be added
    Dim strSQL               'Holds the SQL query to query the database 
    
    'Create an ADO connection object
    Set adoCon = Server.CreateObject("ADODB.Connection")
    
    'Set an active connection to the Connection object using a DSN-less connection
    adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("guestbook.mdb")
    
    'Create an ADO recordset object
    Set rsAddComments = Server.CreateObject("ADODB.Recordset")
    
    'Initialise the strSQL variable with an SQL statement to query the database
    strSQL = "SELECT tblComments.Comments,tblUserList.userFullName FROM tblUserList,tblComments;"
    
    'Set the cursor type we are using so we can navigate through the recordset
    rsAddComments.CursorType = 2
    
    'Set the lock type so that the record is locked by ADO when it is updated
    rsAddComments.LockType = 3 
     
    'Open the recordset with the SQL query 
    rsAddComments.Open strSQL, adoCon 
    
    'Tell the recordset we are adding a new record to it
    rsAddComments.AddNew
    
    'Add a new record to the recordset
    rsAddComments.Fields("userFullName") = Request.Form("formName")
    rsAddComments.Fields("Comments") = Request.Form("formComments") 
     
    'Write the updated recordset to the database
    rsAddComments.Update
    
    'Reset server objects
    rsAddComments.Close
    Set rsAddComments = Nothing
    Set adoCon = Nothing 
     
    'Redirect to the guestbook.asp page
    Response.Redirect "index.asp"
    %>
    Line 36: rsAddComments.Update

    Now I have only changed one line as far as I can see in this code and thats the strSQL line (line 17)
    Code:
    strSQL = "SELECT tblComments.Comments,tblUserList.userFullName FROM tblUserList,tblComments;"
    In my other version which works it was selecting from one table, not two and I have the same permissions on both files and folders so I can't understand why I get this error now....

    Can anyone help please.

    O.
    Failure happens, its what you learn from it that is important!

  2. #2
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i'm guessing here since i don't normally use the ADO AddNew, but I'm betting that you can't do that with 2 tables open. if it were me, i'd create 2 update statements and then run them separately.

  3. #3
    SitePoint Evangelist OwainGDWilliams's Avatar
    Join Date
    Aug 2005
    Location
    Scotland, UK
    Posts
    435
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why didnt I think about about that! Will give it a go today...

    Cheers,
    O.
    Failure happens, its what you learn from it that is important!

  4. #4
    SitePoint Evangelist OwainGDWilliams's Avatar
    Join Date
    Aug 2005
    Location
    Scotland, UK
    Posts
    435
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yip, it works, it seems you can call on a database using one strSQL but the minute you want to write to it you need to split them in to two connections....

    O.
    Failure happens, its what you learn from it that is important!

  5. #5
    SitePoint Enthusiast rusagar's Avatar
    Join Date
    Jan 2007
    Location
    Kathmandu, Nepal
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Make sure the folder your database is in has WRITE permissions for the IUSER account.

    This tell's you how:

    http://www.pagedowntech.com/faq/faq.asp?faqid=18

  6. #6
    SitePoint Evangelist OwainGDWilliams's Avatar
    Join Date
    Aug 2005
    Location
    Scotland, UK
    Posts
    435
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers.

    O.
    Failure happens, its what you learn from it that is important!


Bookmarks

Posting Permissions

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