Asp Forum - Ignore & continue for ADO 'field is too small' errors

Jim Archer

1/16/2012 7:24:00 PM

Hi all. I'd post this on the VB6 ADO or VB6 database groups, but it seems dead
over there. I've also done quite a bit of Googling about this, but strangely
only found one post way back in 2004, with no replies and he was using C++.

Anyway, while working in ADO recently (am new but learning fast), I ran into
trouble trying to update a text field with more characters than the DefinedSize
(using rs.Fields("FieldName").Value =). Trying to edit other fields after that
(same recordset) causes the "Update or CancelUpdate without AddNew or Edit"
error, and of course it prevents rs.Update from working as well. The thing is
though, if my program tries to update a numeric field with text, for example, I
get an error, but it allows other fields to be updated just fine and allows
rs.Update to happen.

Now, I could put in a check to see if the field is text, check that the string
is greater than the DefinedSize, then truncate the text to the DefinedSize, but
I'd really like to prevent those steps since I'm updating 100+ fields per second
from date coming in from multiple threads and I'm mostly testing right now. I
just want it to "ignore and continue" like "On Error Resume Next" would in VB
itself instead of totally breaking.

Has anyone ran into this before? And is there maybe a way I can tell ADO to
just ignore the error and continue to allow updates of other fields without
having to re-open the recordset?

--
--------------------------------- --- -- -
Posted with NewsLeecher v5.0 Beta 12
Web @ http://www.newsleecher.c...
------------------- ----- ---- -- -

18 Answers

ralph

1/16/2012 8:36:00 PM

On Mon, 16 Jan 2012 13:24:14 -0600, Jim Archer <nospam@nothanx.com>
wrote:

>Hi all. I'd post this on the VB6 ADO or VB6 database groups, but it seems dead
>over there. I've also done quite a bit of Googling about this, but strangely
>only found one post way back in 2004, with no replies and he was using C++.
>
>Anyway, while working in ADO recently (am new but learning fast), I ran into
>trouble trying to update a text field with more characters than the DefinedSize
>(using rs.Fields("FieldName").Value =). Trying to edit other fields after that
>(same recordset) causes the "Update or CancelUpdate without AddNew or Edit"
>error, and of course it prevents rs.Update from working as well. The thing is
>though, if my program tries to update a numeric field with text, for example, I
>get an error, but it allows other fields to be updated just fine and allows
>rs.Update to happen.
>
>Now, I could put in a check to see if the field is text, check that the string
>is greater than the DefinedSize, then truncate the text to the DefinedSize, but
>I'd really like to prevent those steps since I'm updating 100+ fields per second
>from date coming in from multiple threads and I'm mostly testing right now. I
>just want it to "ignore and continue" like "On Error Resume Next" would in VB
>itself instead of totally breaking.
>
>Has anyone ran into this before? And is there maybe a way I can tell ADO to
>just ignore the error and continue to allow updates of other fields without
>having to re-open the recordset?

You might try not using the Update method.

Background: With Update ADO constructs, behind the scenes, its best
guess as to what an Update Query would look like (from the original
'fetch' query), and tries to track what has been changed so it knows
what to "update" when called upon. When it gets an error ADO (at the
top-level) doesn't really care what happened, where, or why - it just
knows the query is 'bad'.

So you might create your own separate update queries, eg. put the
troublesone one in one query, run, catch error, and repair or skip,
and then go on to update the rest.

You didn't mention the database but you could make them SPs so they
are a bit quicker. (ADO's construct is semi pre-parsed.)

Frankly I'd fix the original problem now. You will have to address the
issue of bad data sooner or later.
A simple Len and Mid call to truncate a string shouldn't have that
much impact (I think <g>) on over-all performance. Or if "data string
is too long" is a very frequent occurrence then just truncate all
incoming strings. You will have to test on your data.

-ralph

Jim Archer

1/16/2012 9:40:00 PM

Thank you ralph. I'm somewhat new to ADO, so Update seemed to make the most
sense.

The database format I'm using is an Access 2000 compatible MDB. Does that
matter? I'm not sure what an "SP" is, but I take it SP means "semi pre-parsed"?
Even then, I'm not sure what you mean exactly by that. It's kind of over my
head right now.

I've made a simple truncating that checks if the field type is a string, then
makes sure the text is the right length. That does work, but now I'm worried
about all kinds of other possible errors that I'll have to watch for, which will
take even more checking code. I don't mind the field not getting written, but I
don't want it to just disgard the entire record of 50 fields just because I
tried writing a field with a string that was too long, or some other error. To
me, this is almost equivalent to not having "On Error Resume Next" in VB itself.
Imagine not being able to "ignore and continue" in VB6 EXEs. I know many coders
who will have "On Error Resume Next" when running as an EXE vs. in an IDE. I
was just looking for the same thing in ADO. :
Jim

In reply to "ralph" who wrote the following:
> On Mon, 16 Jan 2012 13:24:14 -0600, Jim Archer <nospam@nothanx.com>
> wrote:
>
> > Hi all. I'd post this on the VB6 ADO or VB6 database groups, but it seems
> > dead
> > over there. I've also done quite a bit of Googling about this, but
> > strangely
> > only found one post way back in 2004, with no replies and he was using C++.
> >
> > Anyway, while working in ADO recently (am new but learning fast), I ran into
> > trouble trying to update a text field with more characters than the
> > DefinedSize
> > (using rs.Fields("FieldName").Value =). Trying to edit other fields after
> > that
> > (same recordset) causes the "Update or CancelUpdate without AddNew or Edit"
> > error, and of course it prevents rs.Update from working as well. The thing
> > is
> > though, if my program tries to update a numeric field with text, for
> > example, I
> > get an error, but it allows other fields to be updated just fine and allows
> > rs.Update to happen.
> >
> > Now, I could put in a check to see if the field is text, check that the
> > string
> > is greater than the DefinedSize, then truncate the text to the DefinedSize,
> > but
> > I'd really like to prevent those steps since I'm updating 100+ fields per
> > second
> > from date coming in from multiple threads and I'm mostly testing right now.
> > I
> > just want it to "ignore and continue" like "On Error Resume Next" would in
> > VB
> > itself instead of totally breaking.
> >
> > Has anyone ran into this before? And is there maybe a way I can tell ADO to
> > just ignore the error and continue to allow updates of other fields without
> > having to re-open the recordset?
>
> You might try not using the Update method.
>
> Background: With Update ADO constructs, behind the scenes, its best
> guess as to what an Update Query would look like (from the original
> 'fetch' query), and tries to track what has been changed so it knows
> what to "update" when called upon. When it gets an error ADO (at the
> top-level) doesn't really care what happened, where, or why - it just
> knows the query is 'bad'.
>
> So you might create your own separate update queries, eg. put the
> troublesone one in one query, run, catch error, and repair or skip,
> and then go on to update the rest.
>
> You didn't mention the database but you could make them SPs so they
> are a bit quicker. (ADO's construct is semi pre-parsed.)
>
> Frankly I'd fix the original problem now. You will have to address the
> issue of bad data sooner or later.
> A simple Len and Mid call to truncate a string shouldn't have that
> much impact (I think <g>) on over-all performance. Or if "data string
> is too long" is a very frequent occurrence then just truncate all
> incoming strings. You will have to test on your data.
>
> -ralph

--
--------------------------------- --- -- -
Posted with NewsLeecher v5.0 Beta 12
Web @ http://www.newsleecher.c...
------------------- ----- ---- -- -

James Tyler

1/16/2012 10:17:00 PM

I work with DAO, not ADO. So keep that in mind. Also I try to clean up the
data before I put it in DB
In you situation, you have to at least do a Err.Clear if ado raise a error,
otherwise it ignores you from that point on.

ralph

1/16/2012 11:01:00 PM

On Mon, 16 Jan 2012 15:39:37 -0600, Jim Archer <nospam@nothanx.com>
wrote:
>Jim
>
>In reply to "ralph" who wrote the following:
>> On Mon, 16 Jan 2012 13:24:14 -0600, Jim Archer <nospam@nothanx.com>
>> wrote:
>>
>> > Hi all. I'd post this on the VB6 ADO or VB6 database groups, but it seems
>> > dead
>> > over there. I've also done quite a bit of Googling about this, but
>> > strangely
>> > only found one post way back in 2004, with no replies and he was using C++.
>> >
>> > Anyway, while working in ADO recently (am new but learning fast), I ran into
>> > trouble trying to update a text field with more characters than the
>> > DefinedSize
>> > (using rs.Fields("FieldName").Value =). Trying to edit other fields after
>> > that
>> > (same recordset) causes the "Update or CancelUpdate without AddNew or Edit"
>> > error, and of course it prevents rs.Update from working as well. The thing
>> > is
>> > though, if my program tries to update a numeric field with text, for
>> > example, I
>> > get an error, but it allows other fields to be updated just fine and allows
>> > rs.Update to happen.
>> >
>> > Now, I could put in a check to see if the field is text, check that the
>> > string
>> > is greater than the DefinedSize, then truncate the text to the DefinedSize,
>> > but
>> > I'd really like to prevent those steps since I'm updating 100+ fields per
>> > second
>> > from date coming in from multiple threads and I'm mostly testing right now.
>> > I
>> > just want it to "ignore and continue" like "On Error Resume Next" would in
>> > VB
>> > itself instead of totally breaking.
>> >
>> > Has anyone ran into this before? And is there maybe a way I can tell ADO to
>> > just ignore the error and continue to allow updates of other fields without
>> > having to re-open the recordset?
>>
>> You might try not using the Update method.
>>
>> Background: With Update ADO constructs, behind the scenes, its best
>> guess as to what an Update Query would look like (from the original
>> 'fetch' query), and tries to track what has been changed so it knows
>> what to "update" when called upon. When it gets an error ADO (at the
>> top-level) doesn't really care what happened, where, or why - it just
>> knows the query is 'bad'.
>>
>> So you might create your own separate update queries, eg. put the
>> troublesone one in one query, run, catch error, and repair or skip,
>> and then go on to update the rest.
>>
>> You didn't mention the database but you could make them SPs so they
>> are a bit quicker. (ADO's construct is semi pre-parsed.)
>>
>> Frankly I'd fix the original problem now. You will have to address the
>> issue of bad data sooner or later.
>> A simple Len and Mid call to truncate a string shouldn't have that
>> much impact (I think <g>) on over-all performance. Or if "data string
>> is too long" is a very frequent occurrence then just truncate all
>> incoming strings. You will have to test on your data.
>>
>> -ralph

>Thank you ralph. I'm somewhat new to ADO, so Update seemed to make the most
>sense.
>
>The database format I'm using is an Access 2000 compatible MDB. Does that
>matter? I'm not sure what an "SP" is, but I take it SP means "semi pre-parsed"?
>Even then, I'm not sure what you mean exactly by that. It's kind of over my
>head right now.
>
>I've made a simple truncating that checks if the field type is a string, then
>makes sure the text is the right length. That does work, but now I'm worried
>about all kinds of other possible errors that I'll have to watch for, which will
>take even more checking code. I don't mind the field not getting written, but I
>don't want it to just disgard the entire record of 50 fields just because I
>tried writing a field with a string that was too long, or some other error. To
>me, this is almost equivalent to not having "On Error Resume Next" in VB itself.
>Imagine not being able to "ignore and continue" in VB6 EXEs. I know many coders
>who will have "On Error Resume Next" when running as an EXE vs. in an IDE. I
>was just looking for the same thing in ADO. :>

[Corrected top post]

By SP I meant a "Store Procedures".
As a developer you absolutely have to add them to your work set.

Here is a quick tutorial on SPs:
http://www.vb6.us/tutorials/using-ado-and-stored-proc...

The Access (mdb) example uses Access Query objects. If you have MS
Access you can create these and add to your .mdb database.

But I provided it mostly as an over-view. You can also create and use
ADODB Stored Procedures as a Command object. (ie, MS Access not
required.)
Here is something I found that explains how that is done:
http://www.tek-tips.com/viewthread.cfm?...

(I'm having a bit of a problem finding better sites. All I get is .Net
stuff, plus few VBers use ADODB SPs with mdb as it is easier to create
a MS Access Query object. <g>)

These seem interesting...
http://support.microsoft.com...
http://vbrad.com/article.a...
http://www.vbi.org/Items/article....
http://msdn.microsoft.com/en-us/librar...(v=office.11).aspx

Anyway this should give you a good idea of the how and why of stored
procedures. Any other problems - holler back.

-ralph

James Tyler

1/16/2012 11:07:00 PM

i don't think you can use SP with access.

ralph

1/16/2012 11:32:00 PM

On Mon, 16 Jan 2012 17:17:06 -0500, "Phil Hunt" <aaa@aaa.com> wrote:

>I work with DAO, not ADO. So keep that in mind. Also I try to clean up the
>data before I put it in DB
>In you situation, you have to at least do a Err.Clear if ado raise a error,
>otherwise it ignores you from that point on.
>
>
>

To amplify, there are actually two slightly different set of errors
from "ADO".

1) Errors the ADODB Provider reports. These are found in the
ADO Errors Collection in the Connection object. Each error in the
collection is often called an Error Object.
http://support.microsoft.com...
These errors may or may not invoke a "ADO error".

2) ADO errors raised as a 'normal' VB Runtime Error.

How do you know which is which, you ask? Well, mostly by experience,
but it really doesn't matter - you have to be ready to handle both.

For example, if the mdb file is across a wire and the server went down
you would get an ADODB Provider error. If you set an invalid
Connection property you would likely get an ADO error. If there is a
corrupted mdb file and you called a Query object you would probably
get both - a VB runtime error and entries in the Error Collection.

Always check and enumerate the ADODB Errors Collection even on a VB
error.

The Errors Collection does not contain a history. Only the last set of
error objects the Provider ran into when it attempted to honor a
request. There are several error objects because the Provider can
report numerous problems.

It is important to enumerate the entire collection. Subsquent errors
often provide more information than the top one.

The error collection is not cleared with every request. Only when
there is another error. You can manually clear the Collection using
the .Clear method as Phil advised.

-ralph

ralph

1/17/2012 12:23:00 AM

On Mon, 16 Jan 2012 18:07:20 -0500, "Phil Hunt" <aaa@aaa.com> wrote:

>i don't think you can use SP with access.
>

I can see where I might have given that impression. I mentioned "ADO
Stored Procedures" but didn't match up the correct URL. All the
examples I gave used databases that were "storing" the procedure.

To create an "ADO Stored Procedure", one that is precompiled, and
accept parameters, etc. , and is stored within your program - You need
to use the .Prepared and .Name Properties:

[Warning! Air Code!]
Set cnn=Server.CreateObject("ADODB.Connection")
cnn.Provider="Microsoft.Jet.OLEDB.4.0"
cnn.Open "c:/mydatabase.mdb"

Set cmd=Server.CreateObject("ADODB.Command")
' This has to be set here before the connection
cmd.Name = "MyUpdateSP"
Set cmd.ActiveConnection = cnn
cmd.CommandText = "UPDATE table_name SET fld1=value,
fld2=value2"
cmd.Prepared = True ' Note: the ADO default is False
' so every time the query is called
' it is reparsed and compiled
...
Then keep that command object handy. Calling it would be the same as
the other examples.

I apologize for the confusion I likely caused the OP and you. My only
excuse is I use the Data Environment and multiple 3rd party toys, as
well as work with multiple databases - I often get confused. <bg>

Note to the OP, the query is compiled on first call. So the first use
may be a tad slow, but after that they will all be faster. You
probably won't notice it as .Update does exactly the same thing.

-ralph

Jim Archer

1/17/2012 12:28:00 AM

Thanks Phil. Oh yes, I have tried doing Err.Clear each time, but it doesn't
seem to help the "Update or CancelUpdate without AddNew or Edit" errors and all
field edits afterwards seem to be ignored. I should probably make some very
tight source to demonstrate the problem. Right now it's in a large project with
a lot of non-ADO code between.

It's definitely better to massage the data and only supply clean data to the
database, but for testing or doing something quick and dirty, I just want it to
ignore and and go. Other errors like supplying text to a numeric field also
cause an error, but I can fill in other fields afterward with no problem. So
far it seems that only a "field is too small" error completely breaks things.

In reply to "Phil Hunt" who wrote the following:
> I work with DAO, not ADO. So keep that in mind. Also I try to clean up the
> data before I put it in DB
> In you situation, you have to at least do a Err.Clear if ado raise a error,
> otherwise it ignores you from that point on.

--
--------------------------------- --- -- -
Posted with NewsLeecher v5.0 Beta 12
Web @ http://www.newsleecher.c...
------------------- ----- ---- -- -

Jim Archer

1/17/2012 12:56:00 AM

That interesting ralph! I'm not sure that would work for what I'm doing though.
See, I'm pulling over 100 fields from 3 different one-to-one tables and
performing edits on each field as I go. Sorry I didn't mention that before. I
use a string variable for the field name and a same variant variable for the
value. That way I can use a single variable and change it every time. If I
were to create one big UPDATE string, it would be a couple thousand characters
long and it would have to contain a separate variable for each field name and
each value.

Until I get more time to figure out a way to ignore and continue "field is too
small" errors, I'm now doing the following. I don't understand why I don't have
to worry about trying to fill in numeric & date fields with incorrect data
types, but I do when a text field is too small for the data. You'd think ADO
would just ignore it like other errors, or even have a built-in truncating
features.

So this is what I'm doing now, and it works like I knew it would, I just didn't
want to have to add this. I'm worried what other checks I need to perform.
It's just going to take trial and error I guess.

Dim sField as String, vData as Variant

Do
..Code to change sField and vData to
..whatever I need to goes here
'Check for data type and truncate string value if needed
If rs.Fields(sField).Type = adVarWChar Then
If Len(vData) > rs.Fields(sField).DefinedSize Then
vData = Left(vData, rs.Fields(sField).DefinedSize)
End If
End If
Loop Until x = y

Jim

In reply to "ralph" who wrote the following:
> On Mon, 16 Jan 2012 18:07:20 -0500, "Phil Hunt" <aaa@aaa.com> wrote:
>
> > i don't think you can use SP with access.
> >
>
> I can see where I might have given that impression. I mentioned "ADO
> Stored Procedures" but didn't match up the correct URL. All the
> examples I gave used databases that were "storing" the procedure.
>
> To create an "ADO Stored Procedure", one that is precompiled, and
> accept parameters, etc. , and is stored within your program - You need
> to use the .Prepared and .Name Properties:
>
> [Warning! Air Code!]
> Set cnn=Server.CreateObject("ADODB.Connection")
> cnn.Provider="Microsoft.Jet.OLEDB.4.0"
> cnn.Open "c:/mydatabase.mdb"
>
> Set cmd=Server.CreateObject("ADODB.Command")
> ' This has to be set here before the connection
> cmd.Name = "MyUpdateSP"
> Set cmd.ActiveConnection = cnn
> cmd.CommandText = "UPDATE table_name SET fld1=value,
> fld2=value2"
> cmd.Prepared = True ' Note: the ADO default is False
> ' so every time the query is called
> ' it is reparsed and compiled
> ...
> Then keep that command object handy. Calling it would be the same as
> the other examples.
>
> I apologize for the confusion I likely caused the OP and you. My only
> excuse is I use the Data Environment and multiple 3rd party toys, as
> well as work with multiple databases - I often get confused. <bg>
>
> Note to the OP, the query is compiled on first call. So the first use
> may be a tad slow, but after that they will all be faster. You
> probably won't notice it as .Update does exactly the same thing.
>
> -ralph

--
--------------------------------- --- -- -
Posted with NewsLeecher v5.0 Beta 12
Web @ http://www.newsleecher.c...
------------------- ----- ---- -- -

Henning

1/17/2012 1:19:00 AM

Inline

"Jim Archer" <nospam@nothanx.com> skrev i meddelandet
news:UYKdnZNIGYOpWonSnZ2dnUVZ_rudnZ2d@giganews.com...
> That interesting ralph! I'm not sure that would work for what I'm doing
> though.
> See, I'm pulling over 100 fields from 3 different one-to-one tables and
> performing edits on each field as I go. Sorry I didn't mention that
> before. I
> use a string variable for the field name and a same variant variable for
> the
> value. That way I can use a single variable and change it every time. If
> I
> were to create one big UPDATE string, it would be a couple thousand
> characters
> long and it would have to contain a separate variable for each field name
> and
> each value.
>
> Until I get more time to figure out a way to ignore and continue "field is
> too
> small" errors, I'm now doing the following. I don't understand why I
> don't have
> to worry about trying to fill in numeric & date fields with incorrect data
> types, but I do when a text field is too small for the data. You'd think
> ADO
> would just ignore it like other errors, or even have a built-in truncating
> features.
>
> So this is what I'm doing now, and it works like I knew it would, I just
> didn't
> want to have to add this. I'm worried what other checks I need to
> perform.
> It's just going to take trial and error I guess.
>
>
> Dim sField as String, vData as Variant
>
> Do
> .Code to change sField and vData to
> .whatever I need to goes here
> 'Check for data type and truncate string value if needed
> If rs.Fields(sField).Type = adVarWChar Then

I would do it like:
MyProperlyDeclaredVar = rs.Fields(sField).DefinedSize

And it could (don't know) be faster to skip the If Len(vData)... and always
truncate.

> If Len(vData) > MyProperlyDeclaredVar Then
> vData = Left(vData, MyProperlyDeclaredVar)
> End If
> End If
> Loop Until x = y
>
>
> Jim

/Henning

>
>
> In reply to "ralph" who wrote the following:
>> On Mon, 16 Jan 2012 18:07:20 -0500, "Phil Hunt" <aaa@aaa.com> wrote:
>>
>> > i don't think you can use SP with access.
>> >
>>
>> I can see where I might have given that impression. I mentioned "ADO
>> Stored Procedures" but didn't match up the correct URL. All the
>> examples I gave used databases that were "storing" the procedure.
>>
>> To create an "ADO Stored Procedure", one that is precompiled, and
>> accept parameters, etc. , and is stored within your program - You need
>> to use the .Prepared and .Name Properties:
>>
>> [Warning! Air Code!]
>> Set cnn=Server.CreateObject("ADODB.Connection")
>> cnn.Provider="Microsoft.Jet.OLEDB.4.0"
>> cnn.Open "c:/mydatabase.mdb"
>>
>> Set cmd=Server.CreateObject("ADODB.Command")
>> ' This has to be set here before the connection
>> cmd.Name = "MyUpdateSP"
>> Set cmd.ActiveConnection = cnn
>> cmd.CommandText = "UPDATE table_name SET fld1=value,
>> fld2=value2"
>> cmd.Prepared = True ' Note: the ADO default is False
>> ' so every time the query is called
>> ' it is reparsed and compiled
>> ...
>> Then keep that command object handy. Calling it would be the same as
>> the other examples.
>>
>> I apologize for the confusion I likely caused the OP and you. My only
>> excuse is I use the Data Environment and multiple 3rd party toys, as
>> well as work with multiple databases - I often get confused. <bg>
>>
>> Note to the OP, the query is compiled on first call. So the first use
>> may be a tad slow, but after that they will all be faster. You
>> probably won't notice it as .Update does exactly the same thing.
>>
>> -ralph
>
>
>
>
> --
> --------------------------------- --- -- -
> Posted with NewsLeecher v5.0 Beta 12
> Web @ http://www.newsleecher.c...
> ------------------- ----- ---- -- -
>

microsoft.public.vb.general.discussion

Ignore & continue for ADO 'field is too small' errors

Jim Archer

ralph

Jim Archer

James Tyler

ralph

James Tyler

ralph

ralph

Jim Archer

Jim Archer

Henning

x Login to ForumsZone