Can't write to recordset

This is a multi-part message in MIME format.

------=_NextPart_000_0013_01C61837.3F181A90
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<%
[at] LANGUAGE=3D"VBSCRIPT"
%>
<%
Option explicit
response.expires =3D 0
%>


<%
'Data MUST be validated first

Dim cnGoodsInStock , rsCompanies, rsSuppliers
Dim Cn, sql, Rs

Set cnGoodsInStock =3D OpenConnToGoodsInStock()

set Rs =3D Server.CreateObject("ADODB.Recordset")
sql=3D"SELECT * FROM Companies "
sql =3D sql & " ORDER BY Companies.CompanyName "
Rs.open Sql,cnGoodsInStock,adOpenDynamic,adCmdText
Set rsCompanies=3D Rs

'Set rsCompanies=3D OpenCompanies(CnGoodsInStock, adOpenDynamic)
'Set rsSuppliers =3D OpenSuppliers(CnGoodsInStock, adOpenDynamic)
rsCompanies.Addnew

Error Type:
ADODB.Recordset (0x800A0CB3)
Current Recordset does not support updating. This may be a limitation of =
the provider, or of the selected locktype.

HELP - this code opens the record set ok. Data can be read - Why won't =
ir let me write?
(It's an access database Cn > UserId =3D Admin Password =3D ""

Jim Bunton

------=_NextPart_000_0013_01C61837.3F181A90
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1528" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2><% <BR> [at] LANGUAGE=3D"VBSCRIPT" =

<BR>%><BR><% <BR> Option explicit<BR> response.expires =
=3D
0<BR>%><BR><BR><BR><%<BR>'Data MUST be validated
first</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Dim cnGoodsInStock , rsCompanies,
rsSuppliers<BR>Dim Cn, sql, Rs</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2> Set cnGoodsInStock =3D
OpenConnToGoodsInStock()</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2> set Rs =3D
Server.CreateObject("ADODB.Recordset")<BR> sql=3D"SELECT * FROM =
Companies
"<BR> sql =3D sql & " ORDER BY Companies.CompanyName =
"<BR> Rs.open
Sql,cnGoodsInStock,adOpenDynamic,adCmdText<BR> Set rsCompanies=3D
Rs</FONT></DIV>
<DIV> </DIV><FONT face=3DArial size=3D2>
<DIV><BR> 'Set rsCompanies=3D OpenCompanies(CnGoodsInStock,
adOpenDynamic)<BR> 'Set rsSuppliers =3D =
OpenSuppliers(CnGoodsInStock,
adOpenDynamic)<BR> <STRONG>rsCompanies.Addnew</STRONG></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><FONT color=3D#ff0000>Error Type:<BR>ADODB.Recordset =
(0x800A0CB3)<BR>Current
Recordset does not support updating. This may be a limitation of the =
provider,
or of the selected locktype.</FONT></DIV>
<DIV><FONT color=3D#ff0000></FONT> </DIV>
<DIV><FONT color=3D#ff0000>HELP - this code opens the record set ok. =
Data can be
read - <STRONG><U>Why won't ir let me write?</U></STRONG></FONT></DIV>
<DIV><FONT color=3D#ff0000><FONT color=3D#000000>(It's an access =
database</FONT>
</FONT><FONT color=3D#000000>Cn > UserId =3D Admin Password =3D =
""</FONT></DIV>
<DIV> </DIV>
<DIV><FONT color=3D#ff0000></FONT></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Jim =
Bunton<BR></FONT></DIV></BODY></HTML>

------=_NextPart_000_0013_01C61837.3F181A90--

"Jim Bunton" <jBunton [at] BlueYonder.co.uk> wrote in message
news:csMxf.111242$D47.100615 [at] fe3.news.blueyonder.co.uk...
<%
[at] LANGUAGE="VBSCRIPT"
%>
<%
Option explicit
response.expires = 0
%>


<%
'Data MUST be validated first

Dim cnGoodsInStock , rsCompanies, rsSuppliers
Dim Cn, sql, Rs

Set cnGoodsInStock = OpenConnToGoodsInStock()

set Rs = Server.CreateObject("ADODB.Recordset")
sql="SELECT * FROM Companies "
sql = sql & " ORDER BY Companies.CompanyName "
Rs.open Sql,cnGoodsInStock,adOpenDynamic,adCmdText
Set rsCompanies= Rs

'Set rsCompanies= OpenCompanies(CnGoodsInStock, adOpenDynamic)
'Set rsSuppliers = OpenSuppliers(CnGoodsInStock, adOpenDynamic)
rsCompanies.Addnew

Error Type:
ADODB.Recordset (0x800A0CB3)
Current Recordset does not support updating. This may be a limitation of the
provider, or of the selected locktype.

HELP - this code opens the record set ok. Data can be read - Why won't ir
let me write?
(It's an access database Cn > UserId = Admin Password = ""

Jim Bunton

Start here:

How do I make my ASP pages more efficient?
http://www.aspfaq.com/show.asp?id=2424

For example,
"Use the adExecuteNoRecords + adCmdText constant
for INSERT, UPDATE and DELETE queries: "

Jim Bunton wrote:
> <%
> [at] LANGUAGE="VBSCRIPT"
> %>
> <%
> Option explicit
> response.expires = 0
> %>
> 
Here is a better way to include your ADO constant definitions:
http://www.aspfaq.com/show.asp?id=2112

> 
> <%
> 'Data MUST be validated first

Good, but where do you do this? Have you snipped out this portion?

>
> Dim cnGoodsInStock , rsCompanies, rsSuppliers
> Dim Cn, sql, Rs
>
> Set cnGoodsInStock = OpenConnToGoodsInStock()
>
> set Rs = Server.CreateObject("ADODB.Recordset")
> sql="SELECT * FROM Companies "
> sql = sql & " ORDER BY Companies.CompanyName "
> Rs.open Sql,cnGoodsInStock,adOpenDynamic,adCmdText

Here is the reason your recordset is readonly. Here is the syntax for the
recordset Open method
(http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthrst open.asp):
recordset.Open Source, ActiveConnection, CursorType, LockType, Options
Five arguments, not four.

You set the LockType parameter to adCmdText which is equivalent to setting
it to 1
(http://msdn.microsoft.com/library/en-us/ado270/htm/mdcstcom mandtypeenum.asp
).
If you look up the lockTypeEnum
(http://msdn.microsoft.com/library/en-us/ado270/htm/mdcstloc ktypeenum.asp)
You will see that 1 is equivalent to adLockReadOnly. So, by setting the
LockType argument to 1, you told ADO to create a readonly recordset. The
line should be:

Rs.open Sql,cnGoodsInStock,adOpenDynamic, _
adLockOptimistic,adCmdText

> Set rsCompanies= Rs

I don't understand the reason for this step. Why not just use Rs?

Now that your question has been answered, I would like to comment on your
practice of using a recordset to maintain data.

In a desktop application, there is nothing wrong with this practice (as long
as you limit the records returned into your recordset by using a WHERE
clause. There is no sense retrieving all the records in your database table
when all you are planning to do is insert a record. Add " WHERE 1=2" to your
sql statement to prevent it from returning any records).

In a web server environment, however, this can kill your application's
scalability. Recordsets are inefficient for anything beyond retrieving
read-only data. SQL DML (Data Modification Language) statements (INSERT,
UPDATE and DELETE) should be used for modifying your data.

My preference is to use saved parameter queries
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=ukS%246S%247CHA.2464%40TK2MSFTNGP11.phx.gbl

http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFT NGP10.phx.gbl&oe=UTF-8&output=gplain

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

to avoid the problems inherent in using dynamic sql, such as sql injection.
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf

But you can avoid dynamic sql without saved queries by using a command
object to pass parameters to a string containing parameter markers
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

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.

Thanks for the reply Bob:

used the 'extra' parameter - all is fine. Good lucid and thorough reply.
Many thanks

[some of the mystification in the code snippet was because it 'really' uses
a function call and I had hacked it to bypass that - I think what had
happenned was the loss of a comma in some of the functions when I ws copyimg
nd pasting from one to others ]

"Bob Barrows [MVP]" <reb01501 [at] NOyahoo.SPAMcom> wrote in message
news:e8QXbOFGGHA.2300 [at] TK2MSFTNGP15.phx.gbl...
> Jim Bunton wrote:
> > <%
> > [at] LANGUAGE="VBSCRIPT"
> > %>
> > <%
> > Option explicit
> > response.expires = 0
> > %>
> > 
> Here is a better way to include your ADO constant definitions:
> http://www.aspfaq.com/show.asp?id=2112
>
> > 
> > <%
> > 'Data MUST be validated first
>
> Good, but where do you do this? Have you snipped out this portion?
>
> >
> > Dim cnGoodsInStock , rsCompanies, rsSuppliers
> > Dim Cn, sql, Rs
> >
> > Set cnGoodsInStock = OpenConnToGoodsInStock()
> >
> > set Rs = Server.CreateObject("ADODB.Recordset")
> > sql="SELECT * FROM Companies "
> > sql = sql & " ORDER BY Companies.CompanyName "
> > Rs.open Sql,cnGoodsInStock,adOpenDynamic,adCmdText
>
> Here is the reason your recordset is readonly. Here is the syntax for the
> recordset Open method
> (http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthrst open.asp):
> recordset.Open Source, ActiveConnection, CursorType, LockType, Options
> Five arguments, not four.
>
> You set the LockType parameter to adCmdText which is equivalent to setting
> it to 1
>
(http://msdn.microsoft.com/library/en-us/ado270/htm/mdcstcom mandtypeenum.asp
> ).
> If you look up the lockTypeEnum
> (http://msdn.microsoft.com/library/en-us/ado270/htm/mdcstloc ktypeenum.asp)
> You will see that 1 is equivalent to adLockReadOnly. So, by setting the
> LockType argument to 1, you told ADO to create a readonly recordset. The
> line should be:
>
> Rs.open Sql,cnGoodsInStock,adOpenDynamic, _
> adLockOptimistic,adCmdText
>
> > Set rsCompanies= Rs
>
> I don't understand the reason for this step. Why not just use Rs?
>
> Now that your question has been answered, I would like to comment on your
> practice of using a recordset to maintain data.
>
> In a desktop application, there is nothing wrong with this practice (as
long
> as you limit the records returned into your recordset by using a WHERE
> clause. There is no sense retrieving all the records in your database
table
> when all you are planning to do is insert a record. Add " WHERE 1=2" to
your
> sql statement to prevent it from returning any records).
>
> In a web server environment, however, this can kill your application's
> scalability. Recordsets are inefficient for anything beyond retrieving
> read-only data. SQL DML (Data Modification Language) statements (INSERT,
> UPDATE and DELETE) should be used for modifying your data.
>
> My preference is to use saved parameter queries
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
>
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=ukS%246S%247CHA.2464%40TK2MSFTNGP11.phx.gbl
>
>
http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFT NGP10.phx.gbl&oe=UTF-8&output=gplain
>
>
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>
>
> to avoid the problems inherent in using dynamic sql, such as sql
injection.
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
> http://www.nextgenss.com/papers/advanced_sql_injection.pdf
>
> But you can avoid dynamic sql without saved queries by using a command
> object to pass parameters to a string containing parameter markers
>
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>
>
> 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.
>
>

Can't write to recordset

Re: Can't write to recordset

Re: Can't write to recordset

Thanks Re: Can't write to recordset