Timeouts (80040E31) on AddNew

I'm trying to add a new record to a recordset, but I get the above error
every time. The error comes immediately, rather than waiting the 30 seconds
or 300 seconds I have the CommandTimeout set for. It occurs for every
combination of LockType, CursorType and CursorLocation (and believe me, I've
tried them all, even the read-only, just because I'm desparate!).

This is an ASP page hitting a SQL Server 2000 database, from an IIS 6
server, both on the intranet inside our firewall on the same domain.

The code:

Set cn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

cn.CursorLocation = adUseClient
cn.ConnectionTimeout = 300
cn.Open "DSN=myDB;UID=sa;PWD=xxxxx"

sql = "select * from myTable"
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.ConnectionTimeout = 300
rs.Open sql, cn

rs.AddNew
.....
assign values to fields, then
....
rs.Update

The error occurs on the AddNew line. I've verified that a recordset is
returned, I've tried different cursor types, I've tried adUseClient on both
the connection and recordset, but nothing seems to work. I've a hunch that
it's not a timeout issue, but something else.

Any ideas?

Re: Timeouts (80040E31) on AddNew

Ugh, stop using AddNew and ADODB.Recordset. Recordsets are for retrieving
data and cause all kinds of locks you don't need. Write a stored procedure
that inserts the row using an INSERT statement, and call the stored
procedure from the connection object.

--
http://www.aspfaq.com/
(Reverse address to reply.)




"salty" wrote in message
news:25145FF7-6598-4F14-8CE3-A47528911EAB@microsoft.com...
> I'm trying to add a new record to a recordset, but I get the above error
> every time. The error comes immediately, rather than waiting the 30
seconds
> or 300 seconds I have the CommandTimeout set for. It occurs for every
> combination of LockType, CursorType and CursorLocation (and believe me,
I've
> tried them all, even the read-only, just because I'm desparate!).
>
> This is an ASP page hitting a SQL Server 2000 database, from an IIS 6
> server, both on the intranet inside our firewall on the same domain.
>
> The code:
>
> Set cn = Server.CreateObject("ADODB.Connection")
> Set rs = Server.CreateObject("ADODB.Recordset")
>
> cn.CursorLocation = adUseClient
> cn.ConnectionTimeout = 300
> cn.Open "DSN=myDB;UID=sa;PWD=xxxxx"
>
> sql = "select * from myTable"
> rs.CursorType = adOpenDynamic
> rs.LockType = adLockOptimistic
> rs.CursorLocation = adUseClient
> rs.ConnectionTimeout = 300
> rs.Open sql, cn
>
> rs.AddNew
> ....
> assign values to fields, then
> ...
> rs.Update
>
> The error occurs on the AddNew line. I've verified that a recordset is
> returned, I've tried different cursor types, I've tried adUseClient on
both
> the connection and recordset, but nothing seems to work. I've a hunch
that
> it's not a timeout issue, but something else.
>
> Any ideas?
>

Re: Timeouts (80040E31) on AddNew

Aaron, I appreciate your reply.

But according to the docs, recordsets are also for adding and updating
records. Otherwise, why have an AddNew method?

In this particular case, it makes more sense to use the recordset to add a
new record, or to update it if the record already exists.

So, all (personal?) preferences aside, how can I make this work?

"Aaron [SQL Server MVP]" wrote:

> Ugh, stop using AddNew and ADODB.Recordset. Recordsets are for retrieving
> data and cause all kinds of locks you don't need. Write a stored procedure
> that inserts the row using an INSERT statement, and call the stored
> procedure from the connection object.

Re: Timeouts (80040E31) on AddNew

> But according to the docs, recordsets are also for adding and updating
> records. Otherwise, why have an AddNew method?

They CAN do that. I CAN ride a tricycle to work, boil an egg on my furnace,
and wear roller skates when driving... that doesn't mean it's the best
method.

> In this particular case, it makes more sense to use the recordset to add a
> new record, or to update it if the record already exists.

I disagree. What is wrong with:

UPDATE row
IF @@ROWCOUNT = 0
INSERT row

> So, all (personal?) preferences aside, how can I make this work?

Do you really think this is only personal preference? Maybe I'm making up
the fact that a recordset takes locks, that stored procedures are more
efficient, etc. etc.?

http://www.aspfaq.com/2191
http://www.aspfaq.com/2424#db
http://www.aspfaq.com/2201

Is it going to kill you to try using a stored procedure instead of going
against this advice because you think it's personal? I doubt you'll find a
single person here who will tell you that ADODB.Recordset is better for
updating rows than a stored procedure or even ad hoc INSERT/UPDATE
statements. Unless you have a different definition of "better"...

Re: Timeouts (80040E31) on AddNew

> sql = "select * from myTable"
> rs.CursorType = adOpenDynamic
> rs.LockType = adLockOptimistic
> rs.CursorLocation = adUseClient
> rs.ConnectionTimeout = 300
> rs.Open sql, cn
>
> rs.AddNew

Do you see the potential problem here? You are opening a recordset that
selects ALL columns from every single row in the table, just so you can add
a row. If the table has a decent amount of rows, I'm certainly not
surprised the script gives up. Some additional links you should look at
regarding your use of a DSN and SELECT *:

http://www.aspfaq.com/2126
http://www.aspfaq.com/2096

I'm not making any of that stuff up, either, FWIW.

Re: Timeouts (80040E31) on AddNew

And what, exactly, is wrong with wearing rollerskates while driving? :>)

Bob Lehmann

"Aaron [SQL Server MVP]" wrote in message
news:%23oUgpzCyEHA.1260@TK2MSFTNGP12.phx.gbl...
> > But according to the docs, recordsets are also for adding and updating
> > records. Otherwise, why have an AddNew method?
>
> They CAN do that. I CAN ride a tricycle to work, boil an egg on my
furnace,
> and wear roller skates when driving... that doesn't mean it's the best
> method.
>
> > In this particular case, it makes more sense to use the recordset to add
a
> > new record, or to update it if the record already exists.
>
> I disagree. What is wrong with:
>
> UPDATE row
> IF @@ROWCOUNT = 0
> INSERT row
>
> > So, all (personal?) preferences aside, how can I make this work?
>
> Do you really think this is only personal preference? Maybe I'm making up
> the fact that a recordset takes locks, that stored procedures are more
> efficient, etc. etc.?
>
> http://www.aspfaq.com/2191
> http://www.aspfaq.com/2424#db
> http://www.aspfaq.com/2201
>
> Is it going to kill you to try using a stored procedure instead of going
> against this advice because you think it's personal? I doubt you'll find
a
> single person here who will tell you that ADODB.Recordset is better for
> updating rows than a stored procedure or even ad hoc INSERT/UPDATE
> statements. Unless you have a different definition of "better"...
>
>

Re: Timeouts (80040E31) on AddNew

salty wrote:

> I'm trying to add a new record to a recordset, but I get the above error
> every time. The error comes immediately, rather than waiting the 30 seconds
> or 300 seconds I have the CommandTimeout set for. It occurs for every
> combination of LockType, CursorType and CursorLocation (and believe me, I've
> tried them all, even the read-only, just because I'm desparate!).
>
> This is an ASP page hitting a SQL Server 2000 database, from an IIS 6
> server, both on the intranet inside our firewall on the same domain.
>
> The code:
>
> Set cn = Server.CreateObject("ADODB.Connection")
> Set rs = Server.CreateObject("ADODB.Recordset")
>
> cn.CursorLocation = adUseClient
> cn.ConnectionTimeout = 300
> cn.Open "DSN=myDB;UID=sa;PWD=xxxxx"
>
> sql = "select * from myTable"
> rs.CursorType = adOpenDynamic
> rs.LockType = adLockOptimistic
> rs.CursorLocation = adUseClient
> rs.ConnectionTimeout = 300
> rs.Open sql, cn
>
> rs.AddNew
> ....
> assign values to fields, then
> ...
> rs.Update
>
> The error occurs on the AddNew line. I've verified that a recordset is
> returned, I've tried different cursor types, I've tried adUseClient on both
> the connection and recordset, but nothing seems to work. I've a hunch that
> it's not a timeout issue, but something else.
>
> Any ideas?
You're doing an unnecessary query and the query is timing out. But all
you really want to do is add new information to a specific table. From
an old post by tenbroeck:

> > Subject: Re: Addnew Without A Query?
> Date: Sat, 29 Apr 2000 11:40:28 -0700
> From: tenbroeck
> Organization: http://www.remarq.com: The World's Usenet/Discussions Start Here
> Newsgroups: microsoft.public.inetserver.asp.db
>
> You don't need a query per se. You just have to reference the
> table you are adding to like so..
>
> Dim objConn, objRS1
> <...Snip>
> objConn.open
> objRS1.open "TableName", objConn ,adOpenKeyset, adLockOptimistic
>
> objRS1.AddNew
> <...Snip>
> add values here.
>
Good Luck,
Michael D. Kersey

Re: Timeouts (80040E31) on AddNew

salty wrote:
> I'm trying to add a new record to a recordset, but I get the above error
> every time. The error comes immediately, rather than waiting the 30 seconds
> or 300 seconds I have the CommandTimeout set for. It occurs for every
> combination of LockType, CursorType and CursorLocation (and believe me, I've
> tried them all, even the read-only, just because I'm desparate!).
>
> This is an ASP page hitting a SQL Server 2000 database, from an IIS 6
> server, both on the intranet inside our firewall on the same domain.
>
> The code:
>
> Set cn = Server.CreateObject("ADODB.Connection")
> Set rs = Server.CreateObject("ADODB.Recordset")
>
> cn.CursorLocation = adUseClient
> cn.ConnectionTimeout = 300
> cn.Open "DSN=myDB;UID=sa;PWD=xxxxx"
>
> sql = "select * from myTable"

I forgot to mention that another way to handle this is to change the
query slightly, e.g., to
sql = "select * from myTable where 0=1"
to guarantee that an empty recordset will be returned. This technique
may work on some providers that don't support certain cursortypes.

> rs.CursorType = adOpenDynamic
> rs.LockType = adLockOptimistic
> rs.CursorLocation = adUseClient
> rs.ConnectionTimeout = 300
> rs.Open sql, cn
>
> rs.AddNew
> ....
> assign values to fields, then
> ...
> rs.Update
>
> The error occurs on the AddNew line. I've verified that a recordset is
> returned, I've tried different cursor types, I've tried adUseClient on both
> the connection and recordset, but nothing seems to work. I've a hunch that
> it's not a timeout issue, but something else.
>
> Any ideas?
>

As others have stated, using AddNew isn't generally as quick and clean
as using SQL INSERT, but it is fairly straightforward and avoids certain
problems of SQL INSERT. For example, certain character sequences can
cause programming problems (e.g., apostrophes) or security problems
(e.g., the pipe command) if not eliminated prior to doing a SQL INSERT;
in contrast, AddNew will merely store those character sequences in the
database.

Good Luck,
Michael D. Kersey

Re: Timeouts (80040E31) on AddNew

Look, it's my fault that you're assuming too much from the code I posted.
This isn't the actual code, but I thought (incorrectly, it seems) it was
close enough to show what is causing my problem and hopefully to get an
answer. More below:

"Aaron [SQL Server MVP]" wrote:

> > sql = "select * from myTable"
> > rs.CursorType = adOpenDynamic
> > rs.LockType = adLockOptimistic
> > rs.CursorLocation = adUseClient
> > rs.ConnectionTimeout = 300
> > rs.Open sql, cn
> >
> > rs.AddNew
>
> Do you see the potential problem here? You are opening a recordset that
> selects ALL columns from every single row in the table, just so you can add
> a row.

Actually, the code is selecting every column (because I actually NEED every
column) from only one row. I know enough to not get every row unless I need
it; I'm not a newbie. My code looks more like this:

sql = "select * from myTable where myKey=" & sMyKey
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.ConnectionTimeout = 300
rs.Open sql, cn
If rs.EOF Then
rs.AddNew
rs.Fields("myKey") = sMyKey
End If
rs.Fields("fldOne") = sDataOne
rs.Fields("fldTwo") = sDataTwo
....
rs.Fields("fldLast") = sDataLast
rs.Update

Locking is not an issue. This system will only be used by 10 people, and
rarely at the same time, and even more rarely will the same record be
accessed by more than one person.

My DSN is not an issue - it's for an intranet server, not an ISP, and is
used by several applications.

> If the table has a decent amount of rows, I'm certainly not
> surprised the script gives up.

As I wrote in my original post, it does NOT give up after some period of
time; it gives up immediately, without a nanosecond of pause.

> I'm not making any of that stuff up, either, FWIW.

I know you're not. I don't mean any disrespect, and while I appreciate you
trying to correct what you perceive is bad code (which is, again, my fault
for not posting the exact code), all I wanted was to know why I was getting a
timeout when it returned immediately with the timeout.

Re: Timeouts (80040E31) on AddNew

Well salty, since you continue to justify not wearing a seatbelt because
you're only driving a block away, I don't think I have any other advice for
you than to try fixing your code like I already suggested.

--
http://www.aspfaq.com/
(Reverse address to reply.)




"salty" wrote in message
news:F5C6604D-7CFA-4C01-80F9-170027D3DFB0@microsoft.com...
> Look, it's my fault that you're assuming too much from the code I posted.
> This isn't the actual code, but I thought (incorrectly, it seems) it was
> close enough to show what is causing my problem and hopefully to get an
> answer. More below:
>
> "Aaron [SQL Server MVP]" wrote:
>
> > > sql = "select * from myTable"
> > > rs.CursorType = adOpenDynamic
> > > rs.LockType = adLockOptimistic
> > > rs.CursorLocation = adUseClient
> > > rs.ConnectionTimeout = 300
> > > rs.Open sql, cn
> > >
> > > rs.AddNew
> >
> > Do you see the potential problem here? You are opening a recordset that
> > selects ALL columns from every single row in the table, just so you can
add
> > a row.
>
> Actually, the code is selecting every column (because I actually NEED
every
> column) from only one row. I know enough to not get every row unless I
need
> it; I'm not a newbie. My code looks more like this:
>
> sql = "select * from myTable where myKey=" & sMyKey
> rs.CursorType = adOpenDynamic
> rs.LockType = adLockOptimistic
> rs.CursorLocation = adUseClient
> rs.ConnectionTimeout = 300
> rs.Open sql, cn
> If rs.EOF Then
> rs.AddNew
> rs.Fields("myKey") = sMyKey
> End If
> rs.Fields("fldOne") = sDataOne
> rs.Fields("fldTwo") = sDataTwo
> ...
> rs.Fields("fldLast") = sDataLast
> rs.Update
>
> Locking is not an issue. This system will only be used by 10 people, and
> rarely at the same time, and even more rarely will the same record be
> accessed by more than one person.
>
> My DSN is not an issue - it's for an intranet server, not an ISP, and is
> used by several applications.
>
> > If the table has a decent amount of rows, I'm certainly not
> > surprised the script gives up.
>
> As I wrote in my original post, it does NOT give up after some period of
> time; it gives up immediately, without a nanosecond of pause.
>
> > I'm not making any of that stuff up, either, FWIW.
>
> I know you're not. I don't mean any disrespect, and while I appreciate
you
> trying to correct what you perceive is bad code (which is, again, my fault
> for not posting the exact code), all I wanted was to know why I was
getting a
> timeout when it returned immediately with the timeout.

Re: Timeouts (80040E31) on AddNew

"Michael D. Kersey" wrote:

> I forgot to mention that another way to handle this is to change the
> query slightly, e.g., to
> sql = "select * from myTable where 0=1"
> to guarantee that an empty recordset will be returned. This technique
> may work on some providers that don't support certain cursortypes.

That's useful information. Thanks!

> As others have stated, using AddNew isn't generally as quick and clean
> as using SQL INSERT, but it is fairly straightforward and avoids certain
> problems of SQL INSERT. For example, certain character sequences can
> cause programming problems (e.g., apostrophes) or security problems
> (e.g., the pipe command) if not eliminated prior to doing a SQL INSERT;
> in contrast, AddNew will merely store those character sequences in the
> database.

That is exactly why I want to avoid using INSERT. And I would like to avoid
using a stored procedue, as suggested by Aaron, because I want all the update
and insert code handled in one app, and not spread across one app and 5 SPs
(one for each table I'm updating).

As for the speed of AddNew, speed isn't an issue for this app, or at least,
the difference will not be noticable. On the other hand, the ESTL program I
wrote in Visual Basic uses AddNew for new records. I ran time comparisons
between SQL INSERTs and AddNew, and there was no appreciable difference, even
when the records created numbered in the hundreds of thousands; AddNew
actually seemed to work faster. I'm thinking that the difference lied in all
the code that checked for apostraphes and replaced them with two apostraphes,
and similar code.

Thanks for your help, Michael. I still don't understand why I'm getting the
timeouts, though. As I just replied to Aaron, I'm not selecting every
record, just one. It comes back instantly in QA, and porting this to a test
VB app gives me no problems. Why the difference when the code runs in ASP?

Re: Timeouts (80040E31) on AddNew

Aaron, why spread my code across two places, the ASP app and several stored
procedures? That makes it harder to maintain, not to mention all the checks
and corrections I have to add to the code to make sure there's no apostraphes
or other problematic characters in the data.

The problem is that I'm getting a timeout error when it's not really timing
out. If you know why, then please tell me. If you don't, then say so.

Re: Timeouts (80040E31) on AddNew

> Aaron, why spread my code across two places, the ASP app and several
stored
> procedures?

Because data manipulation code belongs in the data tier. What is the
difference if you have to scramble through 5 stored procedures or ? One of
the advantages you'll see is that you don't have to open a table, bring any
data back first, etc. This crap about how much manual labor it is to
double-up sngle apostrophes and protect yourself against SQL injection is
just that... using a function for this is going to be far more efficient in
the long run than avoiding stored procedure because of it. The same point I
brought up before still applies... just because the likelihood of getting in
an accident is small doesn't mean it's suddenly okay to not bother
signaling, or wearing your seatbelt, or being sober.

> The problem is that I'm getting a timeout error when it's not really
timing
> out.

The problem is that the only evidence you've shown to support that theory is
a hunch. You couldn't even show us your real code, you had to dumb it down
for us, so I can't verify that you haven't done something silly like set
some timeout property to 1 second.

--
http://www.aspfaq.com/
(Reverse address to reply.)

Re: Timeouts (80040E31) on AddNew

"Aaron [SQL Server MVP]" wrote:

> You couldn't even show us your real code, you had to dumb it down
> for us,

Why assume that I "dumbed down" my code? I simplified it assuming no one
wants to wade through a page full of irrelevant code to get to the point. I
was trying to be considerate.

> so I can't verify that you haven't done something silly like set
> some timeout property to 1 second.

No, I did something silly like setting the timeouts of both the connection
and recordset objects to 300 seconds.

Re: Timeouts (80040E31) on AddNew

Ok, well if you're not even willing to try my suggestions, then I have other
things to do. Good luck!






> No, I did something silly like setting the timeouts of both the connection
> and recordset objects to 300 seconds.

Re: Timeouts (80040E31) on AddNew

I feel like a homeless man being forced to first listen to a long sermon, get
baptized then learn the catechism before getting a simple meal. Thanks for
your, um, "help."

"Aaron [SQL Server MVP]" wrote:

> Ok, well if you're not even willing to try my suggestions, then I have other
> things to do. Good luck!
>
>
>
>
>
>
> > No, I did something silly like setting the timeouts of both the connection
> > and recordset objects to 300 seconds.
>
>
>

Re: Timeouts (80040E31) on AddNew

> I feel like a homeless man being forced to first listen to a long sermon,
get
> baptized then learn the catechism before getting a simple meal. Thanks
for
> your, um, "help."

Just trying to give you some alternative approaches, instead of waving some
magic wand that makes your timeout error (that's not a timeout!) disappear.

I'll be wary next time to devote so much time to someone who clearly doesn't
appreciate it and can only see "his way"...

*PLONK*

Re: Timeouts (80040E31) on AddNew

grab a clue, idiot

he's a volunteer trying to help you, not a priest trying to convert you.
what do you want him to do? fix your problem with voodoo? you know all the
answers, so fix it yourself!!!



"salty" wrote in message
news:AFDE5C7D-CD2F-4A95-84E1-93EF2566856B@microsoft.com...
> I feel like a homeless man being forced to first listen to a long sermon,
get
> baptized then learn the catechism before getting a simple meal. Thanks
for
> your, um, "help."
>
> "Aaron [SQL Server MVP]" wrote:
>
> > Ok, well if you're not even willing to try my suggestions, then I have
other
> > things to do. Good luck!
> >
> >
> >
> >
> >
> >
> > > No, I did something silly like setting the timeouts of both the
connection
> > > and recordset objects to 300 seconds.
> >
> >
> >

Re: Timeouts (80040E31) on AddNew

"willy wonka" wrote:

> grab a clue, idiot

This, from someone whose display name is willy wonka.

> he's a volunteer trying to help you, not a priest trying to convert you.

No, he was definately trying to convert me to the "right" way of doing
things, i.e., his way or the highway. He did not convince me that a total
rewrite (which is what his "solution" would have required) to his way made
the best sense.

> what do you want him to do? fix your problem with voodoo?

Obviously not, you schmuck. I was looking for clues to the error I was
getting. Instead, I got a lecture from an elitist coder on how my approach is
all wrong and inefficient, etc. I asked for help, I got condescension, long
before my own attitude turned sour.

Re: Timeouts (80040E31) on AddNew

salty wrote:
> "Michael D. Kersey" wrote:
>
>
>>I forgot to mention that another way to handle this is to change the
>>query slightly, e.g., to
>>sql = "select * from myTable where 0=1"
>>to guarantee that an empty recordset will be returned. This technique
>>may work on some providers that don't support certain cursortypes.
>
>
> That's useful information. Thanks!
>
>
>>As others have stated, using AddNew isn't generally as quick and clean
>>as using SQL INSERT, but it is fairly straightforward and avoids certain
>>problems of SQL INSERT. For example, certain character sequences can
>>cause programming problems (e.g., apostrophes) or security problems
>>(e.g., the pipe command) if not eliminated prior to doing a SQL INSERT;
>>in contrast, AddNew will merely store those character sequences in the
>>database.
>
>
> That is exactly why I want to avoid using INSERT. And I would like to avoid
> using a stored procedue, as suggested by Aaron, because I want all the update
> and insert code handled in one app, and not spread across one app and 5 SPs
> (one for each table I'm updating).
>
> As for the speed of AddNew, speed isn't an issue for this app, or at least,
> the difference will not be noticable. On the other hand, the ESTL program I
> wrote in Visual Basic uses AddNew for new records. I ran time comparisons
> between SQL INSERTs and AddNew, and there was no appreciable difference, even
> when the records created numbered in the hundreds of thousands; AddNew
> actually seemed to work faster. I'm thinking that the difference lied in all
> the code that checked for apostraphes and replaced them with two apostraphes,
> and similar code.
>
> Thanks for your help, Michael. I still don't understand why I'm getting the
> timeouts, though. As I just replied to Aaron, I'm not selecting every
> record, just one. It comes back instantly in QA, and porting this to a test
> VB app gives me no problems. Why the difference when the code runs in ASP?
>

Also see the ASPFAQ article titled
"Why do I get 80040e31 / 'Timeout Expired' errors?"
at http://aspfaq.com/show.asp?id=2287
(ASPFAQ is maintained by Aaron and is extremely useful).

If the table is _very_ large and is not indexed on "myKey" then the
database must do a table scan (sequentially search the table) and a
timeout is possible. A cure in that case is to create an index on myKey.

Good Luck,
Michael D. Kersey

Re: Timeouts (80040E31) on AddNew

salty wrote:

> Aaron, I appreciate your reply.
>
> But according to the docs, recordsets are also for adding and updating
> records. Otherwise, why have an AddNew method?
>
> In this particular case, it makes more sense to use the recordset to add a
> new record, or to update it if the record already exists.
>
> So, all (personal?) preferences aside, how can I make this work?
>
> "Aaron [SQL Server MVP]" wrote:
>
>
>>Ugh, stop using AddNew and ADODB.Recordset. Recordsets are for retrieving
>>data and cause all kinds of locks you don't need. Write a stored procedure
>>that inserts the row using an INSERT statement, and call the stored
>>procedure from the connection object.
>
>
I just wanted to note for the record that, in some cases, e.g., when you
are concerned about simultaneous access to records, record locks are a
good and even a necessary thing. This mostly applies to update rather
than insert functionality. Although the full functionality of the ADO
recordset object can be mimicked by programming code, the effort is
significant and probably not worthwhile. See
http://groups.google.com/groups?hl=en&lr=&safe=off&selm=8t37 bo014dp%40enews4.newsguy.com
for more details of how the recordset object works.

We've discussed this topic (SQL INSERT or UPDATE versus ADO recordset
methods) in previous threads, in particular
http://www.google.com/groups?hl=en&lr=&threadm=3ACA62BE.EA76 A00B%40hal-pc.org&rnum=4&prev=/groups%3Fas_q%3Dsql%26num%3D1 0%26as_scoring%3Dr%26hl%3Den%26btnG%3DGoogle%2BSearch%26as_e pq%3Daddnew%2B%26as_oq%3D%26as_eq%3D%26as_ugroup%3D%26as_usu bject%3D%26as_uauthors%3Dmdkersey%2540hal-pc.org%2B%26as_ums gid%3D%26lr%3D%26as_drrb%3Dq%26as_qdr%3D%26as_mind%3D12%26as _minm%3D5%26as_miny%3D1981%26as_maxd%3D16%26as_maxm%3D11%26a s_maxy%3D2004%26safe%3Dimages
(and in general
http://www.google.com/groups?hl=en&lr=&scoring=r&as_drrb=q&q =sql+%22addnew+%22+&btnG=Search
)

to quote from one of my posts to the former thread:
In the search for the truth about cursors and why one might use a
recordset instead of a SQL statement, one might look at some of the
excellent archived posts by lothan@newsguy.com. Here's are two good
posts:
http://groups.google.com/groups?q=update+author:lothan%40new sguy.com&num=30&hl=en&lr=&safe=off&scoring=relevance&as_drrb =quick&as_qdr=&as_mind=15&as_minm=8&as_miny=2000&as_maxd=3&a s_maxm=4&as_maxy=2001&rnum=1&seld=973767357&ic=1
http://groups.google.com/groups?q=update+author:lothan%40new sguy.com&num=30&hl=en&lr=&safe=off&scoring=relevance&as_drrb =quick&as_qdr=&as_mind=15&as_minm=8&as_miny=2000&as_maxd=3&a s_maxm=4&as_maxy=2001&rnum=7&seld=993330570&ic=1

Good Luck,
Michael D. Kersey

Re: Timeouts (80040E31) on AddNew

Michael D. Kersey wrote:
> salty wrote:
>
>> Aaron, I appreciate your reply.
>>
>> But according to the docs, recordsets are also for adding and
>> updating records. Otherwise, why have an AddNew method?
>>
>> In this particular case, it makes more sense to use the recordset to
>> add a new record, or to update it if the record already exists.
>>
>> So, all (personal?) preferences aside, how can I make this work?
>>
>> "Aaron [SQL Server MVP]" wrote:
>>
>>
>>> Ugh, stop using AddNew and ADODB.Recordset. Recordsets are for
>>> retrieving data and cause all kinds of locks you don't need. Write
>>> a stored procedure that inserts the row using an INSERT statement,
>>> and call the stored procedure from the connection object.
>>
>>
> I just wanted to note for the record that, in some cases, e.g., when
> you are concerned about simultaneous access to records, record locks
> are a good and even a necessary thing. This mostly applies to update
> rather than insert functionality.

This is true in vb and other client-code technologies.. ASP, however is
server-side technology. Different toolsets are required.

> Although the full functionality of
> the ADO recordset object can be mimicked by programming code, the
> effort is significant and probably not worthwhile. See
>
http://groups.google.com/groups?hl=en&lr=&safe=off&selm=8t37 bo014dp%40enews4.newsguy.com
> for more details of how the recordset object works.

From that link:
"Unfortunately you would have to persist the recordset across pages to get
this to work with ASP. No, I don't recommend doing this due to the
tremendous amount of overhead involved"

I've done this, and he's right: it's not easy.

>
> We've discussed this topic (SQL INSERT or UPDATE versus ADO recordset
> methods) in previous threads, in particular



Good practices in vb do not necessarily translate to good practices in asp.

To the OP's contention that using recordsets for data maintenance can't be a
bad thing or else MS would not have included that functionality in ADO, all
I can say is that ADO was written to be used by many technologies. In some
situations, such as desktop applications (vb, Access), the use of recordsets
is a good thing, especially disconnected recordsets. Web servers are a
different story. You need to optimize your throughput in order to allow the
server to handle the users you throw at it.

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: Timeouts (80040E31) on AddNew

Michael D. Kersey wrote:
> salty wrote:
>
>> Aaron, I appreciate your reply.
>>
>> But according to the docs, recordsets are also for adding and updating
>> records. Otherwise, why have an AddNew method?
>>
>> In this particular case, it makes more sense to use the recordset to
>> add a new record, or to update it if the record already exists.
>>
>> So, all (personal?) preferences aside, how can I make this work?
>>
>> "Aaron [SQL Server MVP]" wrote:
>>
>>
>>> Ugh, stop using AddNew and ADODB.Recordset. Recordsets are for
>>> retrieving
>>> data and cause all kinds of locks you don't need. Write a stored
>>> procedure
>>> that inserts the row using an INSERT statement, and call the stored
>>> procedure from the connection object.
>>
>>
>>
> I just wanted to note for the record that, in some cases, e.g., when you
> are concerned about simultaneous access to records, record locks are a
> good and even a necessary thing. This mostly applies to update rather
> than insert functionality. Although the full functionality of the ADO
> recordset object can be mimicked by programming code, the effort is
> significant and probably not worthwhile. See
> http://groups.google.com/groups?hl=en&lr=&safe=off&selm=8t37 bo014dp%40enews4.newsguy.com
>
> for more details of how the recordset object works.
>
> We've discussed this topic (SQL INSERT or UPDATE versus ADO recordset
> methods) in previous threads, in particular
> http://www.google.com/groups?hl=en&lr=&threadm=3ACA62BE.EA76 A00B%40hal-pc.org&rnum=4&prev=/groups%3Fas_q%3Dsql%26num%3D1 0%26as_scoring%3Dr%26hl%3Den%26btnG%3DGoogle%2BSearch%26as_e pq%3Daddnew%2B%26as_oq%3D%26as_eq%3D%26as_ugroup%3D%26as_usu bject%3D%26as_uauthors%3Dmdkersey%2540hal-pc.org%2B%26as_ums gid%3D%26lr%3D%26as_drrb%3Dq%26as_qdr%3D%26as_mind%3D12%26as _minm%3D5%26as_miny%3D1981%26as_maxd%3D16%26as_maxm%3D11%26a s_maxy%3D2004%26safe%3Dimages
>
> (and in general
> http://www.google.com/groups?hl=en&lr=&scoring=r&as_drrb=q&q =sql+%22addnew+%22+&btnG=Search
> )
>
> to quote from one of my posts to the former thread:
> In the search for the truth about cursors and why one might use a
> recordset instead of a SQL statement, one might look at some of the
> excellent archived posts by lothan@newsguy.com. Here's are two good
> posts:
> http://groups.google.com/groups?q=update+author:lothan%40new sguy.com&num=30&hl=en&lr=&safe=off&scoring=relevance&as_drrb =quick&as_qdr=&as_mind=15&as_minm=8&as_miny=2000&as_maxd=3&a s_maxm=4&as_maxy=2001&rnum=1&seld=973767357&ic=1
>
> http://groups.google.com/groups?q=update+author:lothan%40new sguy.com&num=30&hl=en&lr=&safe=off&scoring=relevance&as_drrb =quick&as_qdr=&as_mind=15&as_minm=8&as_miny=2000&as_maxd=3&a s_maxm=4&as_maxy=2001&rnum=7&seld=993330570&ic=1
Sorry, this last URL should be:
http://groups.google.com/groups?hl=en&lr=&safe=off&selm=8t37 bo014dp%40enews4.newsguy.com

Re: Timeouts (80040E31) on AddNew

Bob Barrows [MVP] wrote:

> Good practices in vb do not necessarily translate to good practices in asp.
> To the OP's contention that using recordsets for data maintenance can't be a
> bad thing or else MS would not have included that functionality in ADO, all
> I can say is that ADO was written to be used by many technologies. In some
> situations, such as desktop applications (vb, Access), the use of recordsets
> is a good thing, especially disconnected recordsets. Web servers are a
> different story. You need to optimize your throughput in order to allow the
> server to handle the users you throw at it.
>
> Bob Barrows

Only if you're at some performance limit, which is rarely encountered.
And in that case a little additional memory or a faster processor is a
cheap and easy fix.

A relatively small number of sites truly stress their web servers on an
hourly basis. Such luckily popular sites will run top-end processors
with maxed-out memory and probably use stored procedures for database
access. But long before that happens, most companies swap out their IIS
server for an Apache server with Perl, JSP or EJB.

What I'm trying to say is that a somewhat-misguided _requirement_ or
_mandate_ to not use the ADO methods is IMO very much a form of
suboptimization.

I don't know why this topic attracts so much heat and so little light.
It's quite obvious to me that each technique has it's advantages and
weaknesses and that either can be used.

In particular, writing SQL that can mimick the full functionality of the
ADO methods in handling simultaneous access to the same record(s) by
multiple users is error-prone and not generally applicable. OTOH the ADO
methods give it to you almost for free. Again I recommend the excellent
archived post by lothan@newsguy.com:
http://groups.google.com/groups?hl=en&lr=&safe=off&selm=8t37 bo014dp%40enews4.newsguy.com
and the associated full thread:
http://groups.google.com/groups?hl=en&lr=&safe=off&threadm=8 t37bo014dp%40enews4.newsguy.com&rnum=1&prev=/groups%3Fhl%3De n%26lr%3D%26safe%3Doff%26selm%3D8t37bo014dp%2540enews4.newsg uy.com

Good Luck,
Michael D. Kersey

Re: Timeouts (80040E31) on AddNew

Bob Barrows [MVP] wrote:
> Michael D. Kersey wrote:
>>I just wanted to note for the record that, in some cases, e.g., when
>>you are concerned about simultaneous access to records, record locks
>>are a good and even a necessary thing. This mostly applies to update
>>rather than insert functionality.
>
> This is true in vb and other client-code technologies.. ASP, however is
> server-side technology. Different toolsets are required.

> Good practices in vb do not necessarily translate to good practices in asp.
>

You are correct and I am dead wrong here!8-(

The ADO object methods don't eliminate simultaneous access complexities
when used in ASP. Instead other methods (timestamps in records, SQL
UPDATEs with complex WHERE clauses referencing previous field values,
etc.) are required to properly handle that situation.

No sooner had I walked away from the PC than I realized that my brain
had time-shifted into the past by a full 10 years.

Again, you are correct and I was wrong.

My revised stance is more limited: that use of the ADO methods is OK but
not as efficient as using SQL.

Good Luck,
Michael D. Kersey

Re: Timeouts (80040E31) on AddNew

> This, from someone whose display name is willy wonka.

Display name suddenly means something? What the hell is "salty"? Does that
make your input more intelligent than someone whose name happens to be Bill
and goes by a nickname? Do you judge all people by their display name? How
do you do it out in the real world when you step away from the computer?
"Oh, I better not talk to you, I can't judge you because you didn't wear
your nametag today."

> No, he was definately trying to convert me to the "right" way of doing
> things, i.e., his way or the highway. He did not convince me that a total
> rewrite (which is what his "solution" would have required) to his way made
> the best sense.

So, what makes the best sense? Waving a hand at code you won't show us and
expecting us to magically eradicate the problems?

> Obviously not, you schmuck. I was looking for clues to the error I was
> getting. Instead, I got a lecture from an elitist coder on how my approach
is
> all wrong and inefficient, etc. I asked for help, I got condescension,
long
> before my own attitude turned sour.

So tell us, are you still getting timeouts? If not, is it because you
actually implemented some of the suggestions that came up in this thread?
If so, are you still unwilling to try the suggestions that came up in this
thread?

Re: Timeouts (80040E31) on AddNew

> Again, you are correct and I was wrong.
>
> My revised stance is more limited: that use of the ADO methods is OK but
> not as efficient as using SQL.

Thanks for the update Michael. Wonder if "salty" is even still around.

Re: Timeouts (80040E31) on AddNew

Are you still here? Can we try again? I agree with Aaron about avoiding
recordset updates and addnews, but I'm a little intrigued by this problem.
See inline.

salty wrote:
> I'm trying to add a new record to a recordset, but I get the above
> error every time. The error comes immediately, rather than waiting
> the 30 seconds or 300 seconds

Can you show us te exact text of the error message? MS sometimes overloads
the error numbers so that they smean different things depending on the
situation.

> I have the CommandTimeout set for. It
> occurs for every combination of LockType, CursorType and
> CursorLocation (and believe me, I've tried them all, even the
> read-only, just because I'm desparate!).
>
> This is an ASP page hitting a SQL Server 2000 database, from an IIS 6
> server, both on the intranet inside our firewall on the same domain.
>
> The code:
>
> Set cn = Server.CreateObject("ADODB.Connection")
> Set rs = Server.CreateObject("ADODB.Recordset")
>
> cn.CursorLocation = adUseClient
> cn.ConnectionTimeout = 300

ConnectionTimeout will not effect CommandTimeout

> cn.Open "DSN=myDB;UID=sa;PWD=xxxxx"

Many problems (not this one, yet. But who knows?) have been solved by
switching to the native OLEDB provider for your database, which looks like
SQL Server in this case? Go to www.able-consulting.com/ado_conn.htm to see
examples of connection strings using the SQLOLEDB provider.

Also, you would be strongly advised to avoid using the sa account in your
applications. The sa account has godlike powers, not only in your database
server, but on the machine on which the server is running. The sa password
should be guarded as if your job depends upon it (it probably does). Best
practice is to create a sql account with limited permissions, and use that
account in your applications.

>
> sql = "select * from myTable"

Should be:
sql = "select from myTable WHERE 1=2"

This will prevent the query from returning any data. You're adding data, you
don't need to retrieve any data.


> rs.CursorType = adOpenDynamic

Probably unnecessary. a static cursor should be sufficient for this.

> rs.LockType = adLockOptimistic
> rs.CursorLocation = adUseClient

This will cause the adOpenDynamic to be ignored. The only cursor available
with adUseClient is a static cursor.

Anyways, you set the CursorLocation at the Connection, there is no need to
do it again here, unless you want a different cursor location for this
recordset.

> rs.ConnectionTimeout = 300

There is no ConnectionTimeout property for a recordset object. Both
ConnectionTimeout and CommandTimeout need to be set on the Connection object
(although CommadTimeout can also be set on a Command object if you wish a
particular query to have a different timeout setting than the other queries
run by your Connection)

> rs.Open sql, cn

Nothing to do with your problem, but it's a best practice to specify the
CommandType argument, as in:

const adCmdText = 1 'this line may not be necessary
rs.Open sql, cn,,,adCmdText

>
> rs.AddNew
> ....
> assign values to fields, then
> ...
> rs.Update
>
> The error occurs on the AddNew line. I've verified that a recordset
> is returned, I've tried different cursor types, I've tried
> adUseClient on both the connection and recordset, but nothing seems
> to work. I've a hunch that it's not a timeout issue, but something

Why? What's the error message? What leads you to believe that it's not a
timeout?

You should do a little more in-depth debugging (substitute your new
connection string in the following):

Set cn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

cn.CursorLocation = adUseClient
cn.ConnectionTimeout = 30
cn.CommandTimeout = 30
On Error Resume Next
cn.Open "DSN=myDB;UID=sa;PWD=xxxxx"
If err<>0 then
response.write "Connection open error: " & err.description
'add err.Number to these response.writes if you think it will be helpful
response.end
end if


const adCmdText = 1 'this line may not be necessary
rs.Open sql, cn,,,adCmdText
If err<>0 then
response.write "Recordset open error: " & err.description
cn.close:set cn=nothing
response.end
end if

rs.AddNew
If err<>0 then
response.write "Recordset AddNew error: " & err.description
cn.close:set cn=nothing
response.end
end if

etc.

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: Timeouts (80040E31) on AddNew

Michael D. Kersey wrote:
> Bob Barrows [MVP] wrote:
>> Michael D. Kersey wrote:
>>> I just wanted to note for the record that, in some cases, e.g., when
>>> you are concerned about simultaneous access to records, record locks
>>> are a good and even a necessary thing. This mostly applies to update
>>> rather than insert functionality.
>>
>> This is true in vb and other client-code technologies.. ASP, however
>> is server-side technology. Different toolsets are required.
>
>> Good practices in vb do not necessarily translate to good practices
>> in asp.
>>
>
> You are correct and I am dead wrong here!8-(
>
> The ADO object methods don't eliminate simultaneous access
> complexities when used in ASP. Instead other methods (timestamps in
> records, SQL UPDATEs with complex WHERE clauses referencing previous
> field values, etc.) are required to properly handle that situation.
>
> No sooner had I walked away from the PC than I realized that my brain
> had time-shifted into the past by a full 10 years.
>
>
It's cool. I was wondering what was going on given some of your previous
posts on this subject. :-)

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: Timeouts (80040E31) on AddNew

> > rs.ConnectionTimeout = 300
>
> There is no ConnectionTimeout property for a recordset object.

....leading me to believe that, further up in the code, there is an on error
resume next. Perhaps this is masking other errors that would give a clue
about the actual problem, rather than the symptom.

A

Re: Timeouts (80040E31) on AddNew

Aaron [SQL Server MVP] wrote:
>>> rs.ConnectionTimeout = 300
>>
>> There is no ConnectionTimeout property for a recordset object.
>
> ...leading me to believe that, further up in the code, there is an on
> error resume next. Perhaps this is masking other errors that would
> give a clue about the actual problem, rather than the symptom.
>
> A
That's why I suggested more in-depth debugging. I cannot imagine an AddNew
causing a CommandTimeout, given that the AddNew operation does not involve a
trip to the database.

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.