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.

/devag/newProjSave.asp, line 321


sqlOCF = "SELECT * FROM DA_OtherCFacilities"
set rstOCF = CreateObject("ADODB.Recordset")
rstOCF.Open sqlOCF, "Provider=SQLOLEDB.1;Persist Security
Info=False;User ID=sa;password=admin@sql;Initial Catalog=GIS;Data
Source=172.16.4.180",1,1

'x = 1
For i = 1 to (T-1)
If request.form("Name" & i) <> "" Then
**line 321 rstOCF.addnew
rstOCF("ProjId") = rsid("ProjID")
rstOCF("FinId") = rsFid("ID")
rstOCF("IName") = request.form("Name" & x)
rstOCF("AccType") = request.form("AType" & x)
rstOCF("AccNo") = request.form("ANo" & x)
rstOCF.updatebatch
End If
'x = x + 1
Next

Any help and/or suggestions highly appreciated. Thanks

Re: Error message

amatuer wrote:
> ADODB.Recordset error '800a0cb3'

My comments will be interspersed with your text, so make sure you read
through to the end. Hopefully you won't get defensive and think I'm
denigrating your abilities: no matter how it appears, everything that
follows should be considered constructive criticism.

>
> Current Recordset does not support updating. This may be a limitation
> of the provider, or of the selected locktype.

Well, I would consider this to be a blessing in disguise: recordsets should
not be used for updating in ASP apps. SQL DML statements (UDATE, INSERT and
DELETE) executed via stored procedures (given that you are using SQL Server)
would be my choice. However ...
>
> /devag/newProjSave.asp, line 321
>
>
> sqlOCF = "SELECT * FROM DA_OtherCFacilities"
> set rstOCF = CreateObject("ADODB.Recordset")
> rstOCF.Open sqlOCF, "Provider=SQLOLEDB.1;Persist Security
> Info=False;User ID=sa;password=admin@sql;Initial Catalog=GIS;Data
> Source=172.16.4.180",1,1

This is part of your problem: you have just opened a keyset (completely
unnecessary), read-only cursor. That last "1" argument determines the
LockType: "1" = adLockReadOnly

You have also committed the ADO programming "sin" of using an implicit
connection. Best practices include using explicit connection objects, like
this:

set cn=CreateObject("adodb.connection")
cn.open "Provider= ..."
rs.open sqlOCF,cn, ...

The reason this is bad is that I see from your code that you've opened a
second recordset, which results in two connections open to your server
instead of just one. See here for why this can be bad:
http://support.microsoft.com/default.aspx?scid=kb;en-us;3284 76

What you should have done is used an explicit connection to open rsid,
assigned the values in rsid("ProjID") and rsFid("ID") to local variables,
allowing you to close rsFid. Never keep a recordset open longer than you
have to. Then use the same explicit connection object to open rstOFC

You have also committed the second cardinal sin of using the sa account in
an application: unless you want a hacker to gain control of your database,
database server and network, you will avoid using sa in application code,
instead creating a limited-rights sql login for use in your apps.

And the third cardinal sin: revealing your sa password on the internet!!! I
hope you've obscured it, but, if not, go RIGHT NOW and change that password!
Guard that sa account as if your job depended on it: it probably does ...
and don't try and give me the excuse that this is a development machine
accessible only to you: good security practices start at the development
stage.

The fourth sin: retrieving all the records from your database table when you
really intend to add records: potentially a massive waste of resources
(actually, your use of a keyset cursor did mitigate this somewhat - but that
is still the inappropriate cursor type for this task). Since you only intend
to add records, you should retrieve an empty recordset. Do this by adding a
WHERE clause to your sql statement that will never be true: something like "
.... WHERE 1=2"

And lastly, a venal sin: using selstar in production code:
http://www.aspfaq.com/show.asp?id=2096


> rstOCF.updatebatch

And lastly, I have seen cases where recordsets opened without specifying a
locktype turned out to support updating (using the recordset.update method),
but I have never seen a case where a recordset supported the updatebatch
method without specifying the adLockBatchOptimistic locktype when opening
it.

You are on the right track however: if you are committed to using a
recordset to perform updates on multiple records (which is what it appears
you are doing), you should use a disconnected recordset along with the
updatebatch method. Here is how I would do it with a recordset:

1. first, create a sql login called app_login in your sql server, add it to
your database and assign to it the minimum rights in that database required
for it to perform the tasks required by your application. Assign it a strong
password, which I will denote by using "xxx" in my code example below.

2. Modify your code as follows:

Set cn=createobject("adodb.connection")
cn.open "Provider=SQLOLEDB.1;" & _
"Persist Security Info=False;" & _
"User ID=app_login;password=xxx;" & _
"Initial Catalog=GIS;Data Source=172.16.4.180"

'open your rsid recordset using cn
set rsid=cn.execute(sqlstatement,,1) '1=adCmdText

projid = rsid("ProjID")
id = rsid("ID")
rsid.close

sqlOCF = "SELECT ProjID,FinID,lName,AccType,Acc " & _
"FROM DA_OtherCFacilities WHERE 1=2"

set rstOCF = CreateObject("ADODB.Recordset")
rstOCF.CursorLocation = 3 'adUseClient
rstOFC.LockType = 4 'adLockBatchOptimistic
rstOCF.Open sqlOCF,cn,,,1 '1=adCmdText

'disconnect the recordset:
set rstOFC.ActiveConnection = Nothing
'close the connection while you add your records
cn.close

'x = 1
For i = 1 to (T-1)
If request.form("Name" & i) <> "" Then
rstOCF.addnew
rstOCF("ProjId") = rsid("ProjID")
rstOCF("FinId") = rsFid("ID")
rstOCF("IName") = request.form("Name" & x)
rstOCF("AccType") = request.form("AType" & x)
rstOCF("AccNo") = request.form("ANo" & x)
rstOCF.update 'use update here
End If
'x = x + 1
Next

'reopen the connection and reconnect the recordset:
cn.open
set rstOFC.ActiveConnection = cn

'Now call updatebatch to send the batched inserts to the database
rstOCF.updatebatch

'clean up
rstOFC.close:set rstOFC.ActiveConnection=nothing
cn.close: set cn=nothing

Here is a link to the ADO documentation which it would behoove you to study,
at least to look up the properties and methods I've illustrated above:
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmscadoa pireference.asp


Again, I strongly suspect that your entire task here could be done without
using a recoredset, but without knowing the details of your database, I
cannot provide specifics.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Error message

"Bob Barrows [MVP]" wrote in message
news:u7$ckWFMHHA.140@TK2MSFTNGP04.phx.gbl...
> amatuer wrote:
>> ADODB.Recordset error '800a0cb3'

[snip]

> And the third cardinal sin: revealing your sa password on the internet!!!
> I hope you've obscured it, but, if not, go RIGHT NOW and change that
> password! Guard that sa account as if your job depended on it: it probably
> does ... and don't try and give me the excuse that this is a development
> machine accessible only to you: good security practices start at the
> development stage.

Easy there Bob, the IP he used is non-routable, per RFC 1918, all addresses
in the segments 172.16.0.0 to 172.31.0.0 are for private use, and are
inaccessible to the public Internet.

Even so, I agree that its careless to expose the sa password anywhere
unintentionally, and also that 'admin' is an extrordinarily weak sa
password. If this SQL Server is accessible to anyone beyond your local
machine, and especially if your IP is mapped via router/firewall to a public
IP, you need to change it yesterday.

-Mark





> The fourth sin: retrieving all the records from your database table when
> you really intend to add records: potentially a massive waste of resources
> (actually, your use of a keyset cursor did mitigate this somewhat - but
> that is still the inappropriate cursor type for this task). Since you only
> intend to add records, you should retrieve an empty recordset. Do this by
> adding a WHERE clause to your sql statement that will never be true:
> something like " ... WHERE 1=2"
>
> And lastly, a venal sin: using selstar in production code:
> http://www.aspfaq.com/show.asp?id=2096
>
>
>> rstOCF.updatebatch
>
> And lastly, I have seen cases where recordsets opened without specifying a
> locktype turned out to support updating (using the recordset.update
> method), but I have never seen a case where a recordset supported the
> updatebatch method without specifying the adLockBatchOptimistic locktype
> when opening it.
>
> You are on the right track however: if you are committed to using a
> recordset to perform updates on multiple records (which is what it appears
> you are doing), you should use a disconnected recordset along with the
> updatebatch method. Here is how I would do it with a recordset:
>
> 1. first, create a sql login called app_login in your sql server, add it
> to your database and assign to it the minimum rights in that database
> required for it to perform the tasks required by your application. Assign
> it a strong password, which I will denote by using "xxx" in my code
> example below.
>
> 2. Modify your code as follows:
>
> Set cn=createobject("adodb.connection")
> cn.open "Provider=SQLOLEDB.1;" & _
> "Persist Security Info=False;" & _
> "User ID=app_login;password=xxx;" & _
> "Initial Catalog=GIS;Data Source=172.16.4.180"
>
> 'open your rsid recordset using cn
> set rsid=cn.execute(sqlstatement,,1) '1=adCmdText
>
> projid = rsid("ProjID")
> id = rsid("ID")
> rsid.close
>
> sqlOCF = "SELECT ProjID,FinID,lName,AccType,Acc " & _
> "FROM DA_OtherCFacilities WHERE 1=2"
>
> set rstOCF = CreateObject("ADODB.Recordset")
> rstOCF.CursorLocation = 3 'adUseClient
> rstOFC.LockType = 4 'adLockBatchOptimistic
> rstOCF.Open sqlOCF,cn,,,1 '1=adCmdText
>
> 'disconnect the recordset:
> set rstOFC.ActiveConnection = Nothing
> 'close the connection while you add your records
> cn.close
>
> 'x = 1
> For i = 1 to (T-1)
> If request.form("Name" & i) <> "" Then
> rstOCF.addnew
> rstOCF("ProjId") = rsid("ProjID")
> rstOCF("FinId") = rsFid("ID")
> rstOCF("IName") = request.form("Name" & x)
> rstOCF("AccType") = request.form("AType" & x)
> rstOCF("AccNo") = request.form("ANo" & x)
> rstOCF.update 'use update here
> End If
> 'x = x + 1
> Next
>
> 'reopen the connection and reconnect the recordset:
> cn.open
> set rstOFC.ActiveConnection = cn
>
> 'Now call updatebatch to send the batched inserts to the database
> rstOCF.updatebatch
>
> 'clean up
> rstOFC.close:set rstOFC.ActiveConnection=nothing
> cn.close: set cn=nothing
>
> Here is a link to the ADO documentation which it would behoove you to
> study, at least to look up the properties and methods I've illustrated
> above:
> http://msdn.microsoft.com/library/en-us/ado270/htm/mdmscadoa pireference.asp
>
>
> Again, I strongly suspect that your entire task here could be done without
> using a recoredset, but without knowing the details of your database, I
> cannot provide specifics.
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>

Re: Error message

Thanks alot Bob.
By the way, the account and pasword that i have posted is not wat I
use, i always use them wen i post. So i can safely say that its now
three sins and not four


Bob Barrows [MVP] wrote:
> amatuer wrote:
> > ADODB.Recordset error '800a0cb3'
>
> My comments will be interspersed with your text, so make sure you read
> through to the end. Hopefully you won't get defensive and think I'm
> denigrating your abilities: no matter how it appears, everything that
> follows should be considered constructive criticism.
>
> >
> > Current Recordset does not support updating. This may be a limitation
> > of the provider, or of the selected locktype.
>
> Well, I would consider this to be a blessing in disguise: recordsets should
> not be used for updating in ASP apps. SQL DML statements (UDATE, INSERT and
> DELETE) executed via stored procedures (given that you are using SQL Server)
> would be my choice. However ...
> >
> > /devag/newProjSave.asp, line 321
> >
> >
> > sqlOCF = "SELECT * FROM DA_OtherCFacilities"
> > set rstOCF = CreateObject("ADODB.Recordset")
> > rstOCF.Open sqlOCF, "Provider=SQLOLEDB.1;Persist Security
> > Info=False;User ID=sa;password=admin@sql;Initial Catalog=GIS;Data
> > Source=172.16.4.180",1,1
>
> This is part of your problem: you have just opened a keyset (completely
> unnecessary), read-only cursor. That last "1" argument determines the
> LockType: "1" = adLockReadOnly
>
> You have also committed the ADO programming "sin" of using an implicit
> connection. Best practices include using explicit connection objects, like
> this:
>
> set cn=CreateObject("adodb.connection")
> cn.open "Provider= ..."
> rs.open sqlOCF,cn, ...
>
> The reason this is bad is that I see from your code that you've opened a
> second recordset, which results in two connections open to your server
> instead of just one. See here for why this can be bad:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;3284 76
>
> What you should have done is used an explicit connection to open rsid,
> assigned the values in rsid("ProjID") and rsFid("ID") to local variables,
> allowing you to close rsFid. Never keep a recordset open longer than you
> have to. Then use the same explicit connection object to open rstOFC
>
> You have also committed the second cardinal sin of using the sa account in
> an application: unless you want a hacker to gain control of your database,
> database server and network, you will avoid using sa in application code,
> instead creating a limited-rights sql login for use in your apps.
>
> And the third cardinal sin: revealing your sa password on the internet!!! I
> hope you've obscured it, but, if not, go RIGHT NOW and change that password!
> Guard that sa account as if your job depended on it: it probably does ...
> and don't try and give me the excuse that this is a development machine
> accessible only to you: good security practices start at the development
> stage.
>
> The fourth sin: retrieving all the records from your database table when you
> really intend to add records: potentially a massive waste of resources
> (actually, your use of a keyset cursor did mitigate this somewhat - but that
> is still the inappropriate cursor type for this task). Since you only intend
> to add records, you should retrieve an empty recordset. Do this by adding a
> WHERE clause to your sql statement that will never be true: something like "
> ... WHERE 1=2"
>
> And lastly, a venal sin: using selstar in production code:
> http://www.aspfaq.com/show.asp?id=2096
>
>
> > rstOCF.updatebatch
>
> And lastly, I have seen cases where recordsets opened without specifying a
> locktype turned out to support updating (using the recordset.update method),
> but I have never seen a case where a recordset supported the updatebatch
> method without specifying the adLockBatchOptimistic locktype when opening
> it.
>
> You are on the right track however: if you are committed to using a
> recordset to perform updates on multiple records (which is what it appears
> you are doing), you should use a disconnected recordset along with the
> updatebatch method. Here is how I would do it with a recordset:
>
> 1. first, create a sql login called app_login in your sql server, add it to
> your database and assign to it the minimum rights in that database required
> for it to perform the tasks required by your application. Assign it a strong
> password, which I will denote by using "xxx" in my code example below.
>
> 2. Modify your code as follows:
>
> Set cn=createobject("adodb.connection")
> cn.open "Provider=SQLOLEDB.1;" & _
> "Persist Security Info=False;" & _
> "User ID=app_login;password=xxx;" & _
> "Initial Catalog=GIS;Data Source=172.16.4.180"
>
> 'open your rsid recordset using cn
> set rsid=cn.execute(sqlstatement,,1) '1=adCmdText
>
> projid = rsid("ProjID")
> id = rsid("ID")
> rsid.close
>
> sqlOCF = "SELECT ProjID,FinID,lName,AccType,Acc " & _
> "FROM DA_OtherCFacilities WHERE 1=2"
>
> set rstOCF = CreateObject("ADODB.Recordset")
> rstOCF.CursorLocation = 3 'adUseClient
> rstOFC.LockType = 4 'adLockBatchOptimistic
> rstOCF.Open sqlOCF,cn,,,1 '1=adCmdText
>
> 'disconnect the recordset:
> set rstOFC.ActiveConnection = Nothing
> 'close the connection while you add your records
> cn.close
>
> 'x = 1
> For i = 1 to (T-1)
> If request.form("Name" & i) <> "" Then
> rstOCF.addnew
> rstOCF("ProjId") = rsid("ProjID")
> rstOCF("FinId") = rsFid("ID")
> rstOCF("IName") = request.form("Name" & x)
> rstOCF("AccType") = request.form("AType" & x)
> rstOCF("AccNo") = request.form("ANo" & x)
> rstOCF.update 'use update here
> End If
> 'x = x + 1
> Next
>
> 'reopen the connection and reconnect the recordset:
> cn.open
> set rstOFC.ActiveConnection = cn
>
> 'Now call updatebatch to send the batched inserts to the database
> rstOCF.updatebatch
>
> 'clean up
> rstOFC.close:set rstOFC.ActiveConnection=nothing
> cn.close: set cn=nothing
>
> Here is a link to the ADO documentation which it would behoove you to study,
> at least to look up the properties and methods I've illustrated above:
> http://msdn.microsoft.com/library/en-us/ado270/htm/mdmscadoa pireference.asp
>
>
> Again, I strongly suspect that your entire task here could be done without
> using a recoredset, but without knowing the details of your database, I
> cannot provide specifics.
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"