Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2008
    Posts
    10

    Question Unanswered: Error on ASP INSERT into MsAccess DB

    Hi,

    I'm getting a peristant error while I'm trying to add records to my database... I'm a novice at ASP and the error messages aint exactly helpful...

    So the error is:

    ADODB.Recordset error '800a0e7d'

    The connection cannot be used to perform this operation. It is either closed or invalid in this context.

    /b/r/a/brainfishes/AOD/home/add_worker.asp, line 11

    My code is:

    Code:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
    
    <%
    Dim DBCON
    dim RS
    dim SQL
    
    DBCON = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="  & 
    "C:\sites\content\b\r\a\brainfishes\db\data.mdb" & ";"
    
    set RS=Server.CreateObject("ADODB.recordset")
    RS.Open DBCON
    
    SQL= "INSERT INTO links (link_name,link_url) VALUES (""" & 
    request.form("link_name") & """',"
    SQL = SQL & "" & request.form("link_url") & """)"
    
    response.write(SQL)
    
    RS.execute SQL
    
    RS.close
    
    response.Redirect("http://brainfishes.brinkster.net/AOD/home/links.asp")
    
    %>
    I've double checked all the field names and the location of the DB, it's all correct... can someone please tell me where I'm going wrong?

    Thanks,

    Brainfishes.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Recordsets are for select commands.

    You do not need one for an insert; you execute the command directly on the connection.

    So you need to create a connection object instead of a recordset.

    http://w3schools.com/ado/default.asp for more inforamtion
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2008
    Posts
    10
    Thanks for the info, got it fixed and running now, managed to add a delete record function too!

    Brainfishes

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Great news! Fancy sharing your working code with the rest of the community?

    George
    Home | Blog

  5. #5
    Join Date
    Mar 2008
    Posts
    10
    Sure,

    My Add code is now:

    Code:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
    
    <%
    dim conn
    
    set conn=Server.CreateObject("ADODB.Connection")
    conn.Provider="Microsoft.Jet.OLEDB.4.0"
    conn.Open "C:\sites\content\b\r\a\brainfishes\db\data.mdb"
    
    SQL= "INSERT INTO links (link_name,link_url) VALUES ('" & request.form("link_name") & "',"
    SQL = SQL & "'" & request.form("link_url") & "')"
    
    conn.execute SQL
    
    conn.close
    
    response.Redirect("http://brainfishes.brinkster.net/AOD/home/links.asp")
    
    %>
    I had to make a few changes to the syntax of my SQL statement too, there were a few too many quotation marks it seems.

    My Delete code is very similar:

    Code:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
    
    <%
    dim conn
    dim rname
    
    set conn=Server.CreateObject("ADODB.Connection")
    conn.Provider="Microsoft.Jet.OLEDB.4.0"
    conn.Open "C:\sites\content\b\r\a\brainfishes\db\data.mdb"
    
    rname = request.form("link_name")
    
    SQL= "DELETE FROM links WHERE link_name='" & rname & "'"
    
    conn.execute SQL
    
    conn.close
    
    response.Redirect("http://brainfishes.brinkster.net/AOD/home/links.asp")
    
    %>
    Once again, thanks for your help... as I said before I'm new at ASP and the error messages it produces are, shall we say, less than helpfull!

    Cheers,

    Brainfishes.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hehe, the error messages have a tendancy to be a little cryptic!

    Thanks for posting back; I am a firm believer of adding the solution to a thread so that future readers may benefit

    If you have any more problems, you know where to come!!
    George
    Home | Blog

Posting Permissions

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