Error Type:ADODB.Recordset (0x800A0E7D) The connection cannot be used to perform this operation. It is either closed or invalid in this context.


Hi:
  I am using a DSN less connection to update the alumni information upon clicking the submit button to an access database.This is the code that I have used.

Dim adocon
Dim strsql

If Request.Form("B1")= "Submit" Then
'-- Create a new SQL string--
strsql = "Select * FROM AlumniMembers"
'-- Create a new recordsheet--
Set adocon = Server.CreateObject("ADODB.Recordset")
adocon.Open "DBQ= " & Server.MapPath("/informs.mdb") &"; Driver = {Microsoft Access Driver(*.mdb)};"  
adocon.Open "DSN=informs"
adocon.CursorType=2
adocon.LockType=3
'--Open recordsheet and execute SQL string
adocon.open strsql,adocon
'--Set recordsheet to add a new record --
adocon.Addnew
      '-- Add records to database from form --      
                  adocon.Fields("Firstname") = Request.Form("firstname")
                adocon.Fields("Lastname") = Request.Form("lastname")
                adocon.Fields("Homeaddress") = Request.Form("address")
                adocon.Fields("city") = Request.Form("city")
                adocon.Fields("state") = Request.Form("state")
                adocon.Fields("zipcode") = Request.Form("zipcode")
                adocon.Fields("e-mail") = Request.Form("email")
                adocon.Fields("web-page") = Request.Form("webpage")
                adocon.Fields("graduation degree") = Request.Form("graddegree")
                adocon.Fields("graduation year") = Request.Form("gradyear")
                adocon.Fields("department") = Request.Form("department")
                adocon.Fields("degree advisor") = Request.Form("advisor")
                adocon.Fields("Professional interest") = Request.Form("interest")
                adocon.Fields("company") = Request.Form("company")
                adocon.Fields("company address") = Request.Form("compaddress")
                adocon.Fields("c_city") = Request.Form("pcity")
                adocon.Fields("c_state") = Request.Form("pstate")
                adocon.Fields("c_zip code") = Request.Form("pzipcode")
                adocon.Fields("c_web page") = Request.Form("pwebpage")
            '--Update the recordsheet
            adocon.Update
            '-- Close and clean
            adocon.Close
            Set adocon = Nothing
                   %>

Upon compiling I get the following error.

"Error Type:ADODB.Recordset (0x800A0E7D) The connection cannot be used to perform this operation. It is either closed or invalid in this context"
.
What is wrong with the code.Please help?
                                                                         Jagdish.l

abhishek_pAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John_LennonCommented:
i guess here are the problem

Set adocon = Server.CreateObject("ADODB.Recordset") '<-- are this a recordset or a connection?
adocon.Open "DBQ= " & Server.MapPath("/informs.mdb") &"; Driver = {Microsoft Access Driver(*.mdb)};"  
adocon.Open "DSN=informs" '<--- Seems like a connection
adocon.CursorType=2
adocon.LockType=3
'--Open recordsheet and execute SQL string
adocon.open strsql,adocon '<-- but you are here using it like a recordset

try this instead
set conn = server.createObject("ADODB.Connection")
Set adocon = Server.CreateObject("ADODB.Recordset")
conn.Open "DBQ= " & Server.MapPath("/informs.mdb") &"; Driver = {Microsoft Access Driver(*.mdb)};"  
'--Open recordsheet and execute SQL string
adocon.open strsql,conn '

--Set recordsheet to add a new record --
adocon.Addnew
     '-- Add records to database from form --    
               adocon.Fields("Firstname") = Request.Form("firstname")
              adocon.Fields("Lastname") = Request.Form("lastname")
              adocon.Fields("Homeaddress") = Request.Form("address")
              adocon.Fields("city") = Request.Form("city")
              adocon.Fields("state") = Request.Form("state")
              adocon.Fields("zipcode") = Request.Form("zipcode")
              adocon.Fields("e-mail") = Request.Form("email")
              adocon.Fields("web-page") = Request.Form("webpage")
              adocon.Fields("graduation degree") = Request.Form("graddegree")
              adocon.Fields("graduation year") = Request.Form("gradyear")
              adocon.Fields("department") = Request.Form("department")
              adocon.Fields("degree advisor") = Request.Form("advisor")
              adocon.Fields("Professional interest") = Request.Form("interest")
              adocon.Fields("company") = Request.Form("company")
              adocon.Fields("company address") = Request.Form("compaddress")
              adocon.Fields("c_city") = Request.Form("pcity")
              adocon.Fields("c_state") = Request.Form("pstate")
              adocon.Fields("c_zip code") = Request.Form("pzipcode")
              adocon.Fields("c_web page") = Request.Form("pwebpage")
          '--Update the recordsheet
          adocon.Update
          '-- Close and clean
          adocon.Close
          Set adocon = Nothing
conn.close
set conn = nothing
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cherukuri30Service EngineerCommented:
thats right remove adocon.Open "DSN=informs"
0
abhishek_pAuthor Commented:

 Upon trying the above code right now I am getting the following error below

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
/Informs/membership_form.asp, line 37


 
0
Cherukuri30Service EngineerCommented:
0
John_LennonCommented:
line 37?

what is line 37 in the code?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.