Current Recordset does not support updating.

Error Type:
ADODB.Recordset (0x800A0CB3)
Current Recordset does not support updating. This may be a limitation of the
provider, or of the selected locktype.
/asp_data3_add.asp, line 30
(line 30 read:s : rsAddComments.AddNew)

<%
'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


'1Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")
'2Set an active connection to the Connection object using a DSN-less
connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("t.mdb")

'3Create an ADO recordset object
Set rsAddComments = Server.CreateObject("ADODB.Recordset")

'4Initialise the strSQL variable with an SQL statement to query the
database
strSQL = "SELECT id,last, first FROM main;"

'5 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("last") = Request.Form("last")
rsAddComments.Fields("first") = Request.Form("first")


'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 "asp_data2.asp"
%>

Re: Current Recordset does not support updating.

barret bonden wrote:
> Error Type:
> ADODB.Recordset (0x800A0CB3)
> Current Recordset does not support updating.

You may not think so at the moment, but this error is actually a good thing!
This error is preventing you from using a recordset/cursor to maintain your
data. Cursors are very inefficient and their overuse will cause you to stay
connected to your database longer than you have to, which will adversely
effect your application's scalability.

What you should be doing is using SQL DML (Data Modification Language -
UPDATE, INSERT, DELETE statements) to do you data modifications. Reserve
your use of recordsets to read-only, forward only cursors to be used for
data retrieval only. See inline for more.


> '1Create an ADO connection object
> Set adoCon = Server.CreateObject("ADODB.Connection")
> '2Set an active connection to the Connection object using a DSN-less
> connection
> adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
> Server.MapPath("t.mdb")

It is highly recommended that you use the native Jet OLE DB provider rather
than the obsolete ODBC driver. Like this:

adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("t.mdb")


>
> '3Create an ADO recordset object
> Set rsAddComments = Server.CreateObject("ADODB.Recordset")
>
> '4Initialise the strSQL variable with an SQL statement to query the
> database
> strSQL = "SELECT id,last, first FROM main;"

Here is the first inefficency: You are retrieving ALL the records from the
table in order to add a new record!!

>
> '5 Set the cursor type we are using so we can navigate through the
> recordset
> rsAddComments.CursorType = 2

I would rather use a static cursor:

rsAddComments.CursorType = 3

>
> '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

Well, I don't see any reason for this cursor to not support updating.
Perhaps switching to the OLE DB provider would fix that, however, let's get
rid of the cursor entirely:

strSQL = "INSERT INTO main(last,first( VALUES (?,?)"
'the ?'s are called ODBC parameter markers
'we will use a command object to pass values to them:

dim cmd, arParms
arParms=Array(Request.Form("last"),Request.Form("first"))
set cmd=createobject("adodb.command")
cmd.CommandText=strSQL
cmd.CommandType=1 'adCmdText
set cmd.ActiveConnection=adoCon
cmd.Execute ,arParms,128 '128=adExecuteNoRecords

adoCon.close

> Set adoCon = Nothing
>
> 'Redirect to the guestbook.asp page
> 'Response.Redirect "asp_data2.asp"
> %>

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Current Recordset does not support updating.

Bob Barrows [MVP] wrote:
> strSQL = "INSERT INTO main(last,first( VALUES (?,?)"

Typo. Should be:
strSQL = "INSERT INTO main(last,first) VALUES (?,?)"

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Expected end of statement Re: Current Recordset does not support updating.

very kind of you -
working with your code - getting an odd error

Expected end of statement
/asp_data3_add.asp, line 11, column 48
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" "Data Source=" &
Server.MapPath("t.mdb")

I've tried getting out all spaces - still having the error



"Bob Barrows [MVP]" wrote in message
news:OE2FcrBAGHA.916@TK2MSFTNGP10.phx.gbl...
> Bob Barrows [MVP] wrote:
> > strSQL = "INSERT INTO main(last,first( VALUES (?,?)"
>
> Typo. Should be:
> strSQL = "INSERT INTO main(last,first) VALUES (?,?)"
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Re: Expected end of statement Re: Current Recordset does not support updating.

barret bonden wrote:
> very kind of you -
> working with your code - getting an odd error
>
> Expected end of statement
> /asp_data3_add.asp, line 11, column 48
> adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" "Data Source=" &
> Server.MapPath("t.mdb")
>
> I've tried getting out all spaces - still having the error
>
>
Here is what I posted. See the difference? :-)
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("t.mdb")
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Expected end of statement Re: Current Recordset does not support updating.

That did it - very gratifying - new data into the file - many thanks -
commng out of Access and it's past time I got into databases on the web ~
I see I have to lean how to force my self to READ connection strings - my
eyes water...

Also playing with Visual Web Developer , but wanted to get some idea of the
CODE behind the data workings
of ASP ...
Now onto eiting and form validation - again many thanks ~

"Bob Barrows [MVP]" wrote in message
news:ulvBRtNAGHA.2560@TK2MSFTNGP12.phx.gbl...
> barret bonden wrote:
> > very kind of you -
> > working with your code - getting an odd error
> >
> > Expected end of statement
> > /asp_data3_add.asp, line 11, column 48
> > adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" "Data Source=" &
> > Server.MapPath("t.mdb")
> >
> > I've tried getting out all spaces - still having the error
> >
> >
> Here is what I posted. See the difference? :-)
> adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & Server.MapPath("t.mdb")
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>