Solved

Current Recordset does not support updating

Posted on 2006-07-15
17
312 Views
Last Modified: 2009-12-16
Hi Experts,

Following on from a previous question I have asked, this was my code for the page which updates the database after adding a new record. It worked fine on IIS on my local server.

<%
myDSN = "DBQ=" & Server.MapPath ("positiondatabase.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"
mySQL = "SELECT * From [JobDetails] ;"
Set JobDetailsRS = Server.CreateObject("ADODB.RecordSet")
JobDetailsRS.Open mySQL, myDSN,1,2,1
JobDetailsRS.AddNew
JobDetailsRS.fields("JobTitle")=Request.form("txtjobtitle")
JobDetailsRS.fields("JobCode")=Request.form("txtjobcode")
JobDetailsRS.fields("JobDateListed")=Request.form("txtjobdatelisted")
JobDetailsRS.fields("JobSuburb")=Request.form("txtjobsuburb")
JobDetailsRS.fields("JobIndustryID")=Request.form("cboJobIndustry")
JobDetailsRS.fields("JobDescription")=Request.form("txtjobdescription")
JobDetailsRS.fields("JobRequirements")=Request.form("txtjobrequirements")
JobDetailsRS.fields("JobTraining")=Request.form("txtjobtraining")
JobDetailsRS.fields("JobContactPerson")=Request.form("txtjobcontactperson")
JobDetailsRS.fields("JobContactEmail1")=Request.form("txtjobcontactemail")
JobDetailsRS.fields("JobContactPhone")=Request.form("txtjobcontactphone")
JobDetailsRS.update
JobDetailsRS.close
set JobDetailsRS = nothing
%>

However, when uploading to our test site, it failed to work, and the webhosts instructions were to use DSN-less connections, and place the databases in a /db folder in the root directory. Following their guidelines I have the following code:

<%
set Conn = server.createobject("ADODB.Connection")
Conn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ= D:\Domains\redstudioweb.com\db\positiondatabase.mdb"
mySQL = "SELECT * From [JobDetails] ;"
Set JobDetailsRS = Conn.Execute(mySQL)
JobDetailsRS.AddNew
JobDetailsRS.fields("JobTitle")=Request.form("txtjobtitle")
JobDetailsRS.fields("JobCode")=Request.form("txtjobcode")
JobDetailsRS.fields("JobDateListed")=Request.form("txtjobdatelisted")
JobDetailsRS.fields("JobSuburb")=Request.form("txtjobsuburb")
JobDetailsRS.fields("JobIndustryID")=Request.form("cboJobIndustry")
JobDetailsRS.fields("JobDescription")=Request.form("txtjobdescription")
JobDetailsRS.fields("JobRequirements")=Request.form("txtjobrequirements")
JobDetailsRS.fields("JobTraining")=Request.form("txtjobtraining")
JobDetailsRS.fields("JobContactPerson")=Request.form("txtjobcontactperson")
JobDetailsRS.fields("JobContactEmail1")=Request.form("txtjobcontactemail")
JobDetailsRS.fields("JobContactPhone")=Request.form("txtjobcontactphone")
JobDetailsRS.update
JobDetailsRS.close
set JobDetailsRS = nothing
%>


Now I am receiving the following error message:
   
    ADODB.Recordset error '800a0cb3'
    Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.
    /client/link/submitaddposition.asp, line 40

Which points to the "JobDetailsRS.AddNew" line

Any ideas on what I am doing wrong? Is it a permission issue? I have read/write permissions (666) on this folder and files, but I can't change them, they go back to the default.
0
Comment
Question by:AndyPaige
17 Comments
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 100 total points
ID: 17114464
Try it with the Jet driver instead:

    Conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= D:\Domains\redstudioweb.com\db\positiondatabase.mdb"

An make sure that you have read/write permissions on the Database file itself. You mention 666, does that mean you are on a UNIX/Linux host ?
0
 

Author Comment

by:AndyPaige
ID: 17114546
As I now have a lovely include file working, (thanks to you), I tried your suggestion re the Jet Driver, but no go. It works fine with displaying records, but doesn't solve the updating issue. It seemed to display the records slower too, would that be right?

Is it something to do with CursorType and LockType, as I am now missing the 1,2,1 that was part of my original code (Isn't that something to do with CursorType?? And I don't know what LockType is)

I am definitely on a Windows host. I was just checking (I thought) the permissions by right-clicking on the folder in my ftp server and giong to Properties.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17114553
>>Set JobDetailsRS = Conn.Execute(mySQL)<<
The Connection's Execute method produces a firehose (Forward-only, Read-Only) cursor and these simply cannot be updated. In case you are curious check the CursorType and LockType for this recordset and you will see what I mean.

You have a couple of choices:
A.  Go back to using the Recordset's Open method.
B.  A better approach is to skip the Recordset object entirely and use a SQL INSERT statement.

Which is your poison?
0
 

Author Comment

by:AndyPaige
ID: 17114573
acperkins, could you please tell me how I would incorporate either of those options with the code as it stands? What is the advantage/disadvantage of each?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17114585
You already have Option A.  It is the first code sample you posted where you use the Recordset's Open method. (Although I probably would not use Pessimistic locking)
0
 

Author Comment

by:AndyPaige
ID: 17114635
I'm afraid I am confused. This could be because it is 1.30am.
I need to keep using the DSN-less connection, (or the Jet Driver method if applicable).

Apparently I need to use a different method of updating without using the Execute statement?
I am not sure how to implement the SQL INSERT statement if that's the best other option?

Any further advice for this very novice programmer would be much appreciated. I need to get some sleep now, but I'll be back tomorrow.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 400 total points
ID: 17114668
Assuming that the Connection string in the second sample is correct (otherwise you would not have got as far as you did: The AddNew line) than use one of the following options:

Option A:
Dim Conn, mySQL, JobDetailsRS

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ= D:\Domains\redstudioweb.com\db\positiondatabase.mdb"
mySQL = "SELECT * From [JobDetails] ;"
JobDetailsRS.Open mySQL, myDSN,1,2,1
JobDetailsRS.AddNew
JobDetailsRS.fields("JobTitle")=Request.form("txtjobtitle")
JobDetailsRS.fields("JobCode")=Request.form("txtjobcode")
JobDetailsRS.fields("JobDateListed")=Request.form("txtjobdatelisted")
JobDetailsRS.fields("JobSuburb")=Request.form("txtjobsuburb")
JobDetailsRS.fields("JobIndustryID")=Request.form("cboJobIndustry")
JobDetailsRS.fields("JobDescription")=Request.form("txtjobdescription")
JobDetailsRS.fields("JobRequirements")=Request.form("txtjobrequirements")
JobDetailsRS.fields("JobTraining")=Request.form("txtjobtraining")
JobDetailsRS.fields("JobContactPerson")=Request.form("txtjobcontactperson")
JobDetailsRS.fields("JobContactEmail1")=Request.form("txtjobcontactemail")
JobDetailsRS.fields("JobContactPhone")=Request.form("txtjobcontactphone")
JobDetailsRS.update
JobDetailsRS.close
Set JobDetailsRS = nothing
Conn.Close
Set Conn = Nothing

Option B:
Dim Conn, mySQL

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ= D:\Domains\redstudioweb.com\db\positiondatabase.mdb"
mySQL =      "INSERT INTO JobDetails (JobTitle, JobCode, JobDateListed, JobSuburb, JobIndustryID, JobDescription, " & _
                  "JobRequirements, JobTraining, JobContactPerson, JobContactEmail1, JobContactPhone) "  & _
      "VALUES (" & Request.form("txtjobtitle") & "," & Request.form("txtjobcode") & "," & Request.form("txtjobdatelisted") & "," & _
            Request.form("txtjobsuburb") & "," & Request.form("cboJobIndustry") & "," & Request.form("txtjobdescription") & "," & _
            Request.form("JobRequirements") & "," & Request.form("JobTraining") & "," & Request.form("JobContactPerson") & "," & _
            Request.form("JobContactEmail1") & "," & Request.form("JobContactPhone") & ")"
Conn.Execute mySQL
Conn.Close
Set Conn = Nothing

This second option is far more efficient, however you need to account for any single quotes in text fields.  One way to allow for single quotes is to escape any single quote by replacing one single quote with two.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17114673
P.S.  The ony difference between Option A and the first code you posted is the Connection String.  You could and should however, change the LockType from adLockPessimistic to adLockOptimistic as in change:
JobDetailsRS.Open mySQL, myDSN,1,2,1

To:
JobDetailsRS.Open mySQL, myDSN, 1, 3, 1

Neither of these code samples was tested, so I will be amazed if you do not find any typos.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17114746
just a supplementary info:
mySQL = "SELECT * From [JobDetails] ;"
should be replaced by:
mySQL = "SELECT * From [JobDetails] WHERE 1 = 0;"
purely for performance issues, as otherwise, you will load the entire table data to the client, while you only want to add a record...

for the other point, I agree with acperkins (points to him)
0
 

Author Comment

by:AndyPaige
ID: 17116459
Thankyou Experts, I am trying Option B as per the code below. I am keeping the connection string here at the moment rather than in the include file (and I've voided the ref to includefile)  - the records are displaying fie on the listing pages, so the connection must be ok.

Microsoft VBScript compilation  error '800a0409'

Unterminated string constant

/client/link/submitaddposition.asp, line 62

mySQL ="INSERT INTO JobDetails (JobTitle, JobCode, JobDateListed, JobSuburb, JobIndustryID, JobDescription, JobRequirements, JobTraining, JobContactPerson, JobContactEmail1, JobContactPhone)


Here is the code
<%
Dim Conn, mySQL
set Conn = server.createobject("ADODB.Connection")
Conn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ= D:\Domains\redstudioweb.com\wwwroot\client\link\db1\positiondatabase.mdb"
mySQL ="INSERT INTO JobDetails (JobTitle, JobCode, JobDateListed, JobSuburb, JobIndustryID, JobDescription, JobRequirements, JobTraining, JobContactPerson, JobContactEmail1, JobContactPhone)
VALUES (" & Request.form("txtjobtitle") & "," & Request.form("txtjobcode") & "," & Request.form("txtjobdatelisted") & "," & Request.form("txtjobsuburb") & "," & Request.form("cboJobIndustry") & "," & Request.form("txtjobdescription") & "," & Request.form("txtjobrequirements") & "," & Request.form("txtjobtraining") & "," & Request.form("txtjobcontactperson") & "," & Request.form("txtjobcontactemail") & "," & Request.form("txtjobcontactphone") & ")"
Conn.Execute mySQL
Conn.Close
Set Conn = Nothing
%>
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17117482
Try it this way:
Dim Conn, mySQL
set Conn = server.createobject("ADODB.Connection")
Conn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ= D:\Domains\redstudioweb.com\wwwroot\client\link\db1\positiondatabase.mdb"
mySQL ="INSERT INTO JobDetails (JobTitle, JobCode, JobDateListed, JobSuburb, JobIndustryID, JobDescription, JobRequirements, JobTraining, JobContactPerson, JobContactEmail1, JobContactPhone) " & _
"VALUES (" & Request.form("txtjobtitle") & "," & Request.form("txtjobcode") & "," & Request.form("txtjobdatelisted") & "," & Request.form("txtjobsuburb") & "," & Request.form("cboJobIndustry") & "," & Request.form("txtjobdescription") & "," & Request.form("txtjobrequirements") & "," & Request.form("txtjobtraining") & "," & Request.form("txtjobcontactperson") & "," & Request.form("txtjobcontactemail") & "," & Request.form("txtjobcontactphone") & ")"
Conn.Execute mySQL
Conn.Close
Set Conn = Nothing

If you are still having problems, post the contents of mySQL as in:
Response.Write mySQL & "</br>"     ' Add this line temporarily
Response.End                                 ' Add this line temporarily
Conn.Execute mySQL
Conn.Close
Set Conn = Nothing
0
 

Author Comment

by:AndyPaige
ID: 17118258
Thankyou for adding another suggestion acperkins,

I did already close off this question (thought I might have pushed my luck)  and asked another which was kind of leading on from this one, and had soldiered on with a couple of other suggetions late last night till I got to the stage where I was getting "Cannot update. Database or object is read-only" errors which I gathered meant that the code was probably working OK but I had permission issues with the database that I have to contact my webhosts about.

I just tried your first suggestion but get:
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1

When I do your last suggestion I get:
INSERT INTO JobDetails (JobTitle, JobCode, JobDateListed, JobSuburb, JobIndustryID, JobDescription, JobRequirements, JobTraining, JobContactPerson, JobContactEmail1, JobContactPhone) VALUES (Office Assistant,123,17/7/06,Frankston,7,blah blah blah,blah blah blah,blah blah blah,John Doe,jdoe@redstudioweb.com.au,123456789)

Is that a good thing?

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17118314
All character columns must be enclosed in single quotes.  Dates are a little more complicated as MS Access allows you to enclose them in #'s.  However, I would suggest you also use single quotes and use an unambiguous date format. So your INSERT statement should look like something like this:

INSERT INTO JobDetails (JobTitle, JobCode, JobDateListed, JobSuburb, JobIndustryID, JobDescription, JobRequirements, JobTraining, JobContactPerson, JobContactEmail1, JobContactPhone) VALUES ('Office Assistant',123,'2006-07-17','Frankston',7,'blah blah blah,blah blah blah,blah blah blah','John Doe','jdoe@redstudioweb.com.au',123456789)

Since I do not know the structure or data types for your table that is a guess at best. You should change it as appropriate

I would try hardcoding that INSERT statement with the literal values.  When you are convinced it does insert the correct values than modify your code to match this statement.
0
 

Author Comment

by:AndyPaige
ID: 17118423
I have left all the fields as text except for the JobDateListed(date) and JobIndustryID(number).

I did what you suggested, and it appeared to insert all the correct values. Now I've adjusted the code back as below (with the single quotes around the text fields) but I get the error "Operation must use an updateable query"

mySQL ="INSERT INTO JobDetails (JobTitle, JobCode, JobDateListed, JobSuburb, JobIndustryID, JobDescription, JobRequirements, JobTraining, JobContactPerson, JobContactEmail1, JobContactPhone) " & _
"VALUES ('" & Request.form("txtjobtitle") & "','" & Request.form("txtjobcode") & "'," & Request.form("txtjobdatelisted") & ",'" & Request.form("txtjobsuburb") & "'," & Request.form("cboJobIndustry") & ",'" & Request.form("txtjobdescription") & "','" & Request.form("txtjobrequirements") & "','" & Request.form("txtjobtraining") & "','" & Request.form("txtjobcontactperson") & "','" & Request.form("txtjobcontactemail") & "','" & Request.form("txtjobcontactphone") & "')"

I have actually managed to get your OPTION A working as far as the "Cannot update. Database or object is read-only" error and I am hoping that if I get the permissions fixed then that will work. As yet I haven't managed to have as much success with the INSERT INTO statement method.
I gather with the INSERT method I am likely to get issues if the user uses single quotes when adding data, or does that apply to both methods?

Sorry, I shouldn't be still asking questions, this one is closed.

This is just the "Add" section I'm working on. I still have the "Edit" and "Delete" to get right, so I'll have to post plenty more questions!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17127726
Post the result of the following:

mySQL ="INSERT INTO JobDetails (JobTitle, JobCode, JobDateListed, JobSuburb, JobIndustryID, JobDescription, JobRequirements, JobTraining, JobContactPerson, JobContactEmail1, JobContactPhone) " & _
"VALUES ('" & Request.form("txtjobtitle") & "','" & Request.form("txtjobcode") & "'," & Request.form("txtjobdatelisted") & ",'" & Request.form("txtjobsuburb") & "'," & Request.form("cboJobIndustry") & ",'" & Request.form("txtjobdescription") & "','" & Request.form("txtjobrequirements") & "','" & Request.form("txtjobtraining") & "','" & Request.form("txtjobcontactperson") & "','" & Request.form("txtjobcontactemail") & "','" & Request.form("txtjobcontactphone") & "')"

In other words as before add:
Response.Write mySQL & "</br>"     ' Add this line temporarily
Response.End                                 ' Add this line temporarily
0
 

Author Comment

by:AndyPaige
ID: 17129419
After posting countless support tickets to my webhost asking them to alter the permissions on the databases, they finally did it, and the whole thing is working great. I now have the choice of either your Option A or B as they both work fine.

Is there any strong reasons why I should use one method over the other?

This is the result of your above code, and without the Response.Write...  it works perfectly.

INSERT INTO JobDetails (JobTitle, JobCode, JobDateListed, JobSuburb, JobIndustryID, JobDescription, JobRequirements, JobTraining, JobContactPerson, JobContactEmail1, JobContactPhone) VALUES ('Office Assistant','OA456',11/07/2006,'Frankston',7,'description of the job','requirements of the job','training included','John Smith','jsmith@somedomain.com','12345678')

I am having a weird issue with the output of the date format, but I will post another question on that.

Thank you very much for your help and patience with a novice, I greatly appreciate it and I have learned a lot.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17132562
>>Is there any strong reasons why I should use one method over the other?<<
Option A.
Advantages:
You do not have to worry about single quotes in character fields.

Option B.
Advantages:
It is less resource intensive.  You no longer have to worry about instantiating the Recordset Object and it should execute faster.
Disadvantage:
You have to escape all character fields for the potential of a single quote.  In other words, you have to replace all single quotes with two single quotes.  For example, instead of:
Request.form("txtjobrequirements")
You would do:
Replace(Request.form("txtjobrequirements"), "'", "''")
Most developers create a function to do this.

Dates need to be preferably formatted in an unambiguous format, as in yyyy-mm-dd
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now