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]" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:e8QXbOFGGHA.2300@xxxxxxxxxxxxxxxxxxxxxxx
> Jim Bunton wrote:
> > <%
> > @ LANGUAGE="VBSCRIPT"
> > %>
> > <%
> > Option explicit
> > response.expires = 0
> > %>
> > <!-- #include file="adovbs.inc" -->
> Here is a better way to include your ADO constant definitions:
> http://www.aspfaq.com/show.asp?id=2112
>
> > <!-- #include file="MyInclude.asp" -->
> > <%
> > '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/mdmthrstopen.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/mdcstcommandtypeenum.asp
> ).
> If you look up the lockTypeEnum
> (http://msdn.microsoft.com/library/en-us/ado270/htm/mdcstlocktypeenum.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%40TK2MSFTNGP10.phx.gbl&oe=UTF-8&output=gplain
>
>
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=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.inetserver.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.
>
>


.

Relevant Pages

  • Re: Emailing a Report
    ... the reason Debug.Print was done was to test the SQL of what is being generated. ... If you will add and change data here, you need to set this up as a main/subform -- or just a main form that DISPLAYS data from Users and allows modifications to Agreements. ... If you are just going to send Agreements, then AgrID should be added to the form RecordSet so you can capture it. ... maybe also some kind of category for this followup type ...
    (microsoft.public.access.modulesdaovba)
  • Re: What is the advantage of Event programming?
    ... >> dice what lumps of data you need. ... which wouldn't work if the recordset has more than ... >My knowledge on using SQL statements is somewhat limited. ... Requesting a limited number of records at a time, was useful in the old days ...
    (microsoft.public.vb.general.discussion)
  • Re: REPOST: One Web Service updates SQL, the other cant
    ... insert is executed a reference to rs.eof is invalid and the program bombs. ... > get recordset back and ADO could generate error here. ... Another instance of the same Web Service code, ... >> Watching both the debugger and the trace, SQL is receiving what I send ...
    (microsoft.public.vb.database.ado)
  • Re: using Command to set Parameters and Recordset to retrive the Query
    ... doesn't the rsData will be interpretate as an input parameter in the SP? ... >> Query and retrive the Recordset so I can use the Paging property ... > Even if this technique of using the parameters in the ORDER BY does work for> you, I suspect that this will defeat your objective of preventing sql> injection. ... See below for a more efficient solution> using a stored procedure. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Help please on Record sets
    ... I tried running it from the query builder after deleting all the ... Just because your "original SQL string was generated in a working ... I had to add a quote to your first Formated Date and Change the quotes to ... recordset or would that lead to more problems? ...
    (microsoft.public.access.modulesdaovba)