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

<%=20
@ LANGUAGE=3D"VBSCRIPT"=20
%>
<%=20
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



charset=3Diso-8859-1">

Re: Can"t write to recordset

"Jim Bunton" wrote in message
news:csMxf.111242$D47.100615@fe3.news.blueyonder.co.uk...
<%
@ 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: "

Re: Can"t write to recordset

Jim Bunton wrote:
> <%
> @ 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 Re: Can"t write to recordset

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]" wrote in message
news:e8QXbOFGGHA.2300@TK2MSFTNGP15.phx.gbl...
> Jim Bunton wrote:
> > <%
> > @ 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.
>
>