Odbc exception - The search key was not found in any record

[Sebastien Lange]

Hi,

I'm executing the sql below several times, but one out of 10000... it throws
an exception! Any idea what could lead to this exception? I don't think it
has something to do with the WHERE statement. Should I try catch the
exception, then close and reopen the db, then try to execute the sql again?

Cannot execute sql: DELETE * FROM [l_sibvar] WHERE state_nbr>=1000000 AND
state_nbr<2000000 ---> System.Data.Odbc.OdbcException: ERROR [HY000]
[Microsoft][ODBC Microsoft Access Driver] The search key was not found in
any record



Thanks,

Sebastien

 

[Sebastien Lange]

Hi Scot,

The sql is executed in a long process, so I can't reproduce it. I'll now try
to close then reopen the connection, but I will not have the answer whether
it works or not, before another exception is reached by one of my users...
This is not the only strange problem I get with odbc; here are some other
exceptions occuring very rarely:
ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet
database engine stopped the process because you and another user are
attempting to change the same data at the same time.

ERROR [07009] [Microsoft][ODBC Microsoft Access Driver] Invalid argument.

ERROR [08004] [Microsoft][ODBC Microsoft Access Driver] Too many active
users.

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] Could not update;
currently locked.

System.InvalidOperationException: BeginTransaction requires an open and
available Connection. The connection's current state is Open, Executing.

Thanks,

Sebastien

Are you executing these one after the other in a loop or is this a longer

process. What happens if you Trap the exception, wait a few seconds and try
it again? Does a time delay

have any effect? Have you tried closing and reopening the connection and re-executing?

Want to know more? Check out the MSDN Library at http://msdn.microsoft.com

or the Microsoft Knowledge Base at http://support.microsoft.com

Scot Rose, MCSD
Microsoft Visual Basic Developer Support
Email : (e-mail address removed) <Remove word online. from address>

This posting is provided "AS IS", with no warranties, and confers no rights.

--------------------
From: "Sebastien Lange" <[email protected]>
Subject: Odbc exception - The search key was not found in any record
Date: Wed, 16 Jul 2003 15:49:48 +0200
Lines: 19
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <[email protected]>
Newsgroups: microsoft.public.dotnet.framework.adonet
NNTP-Posting-Host: alc246.alcatel.be 195.207.101.246
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:55869
X-Tomcat-NG: microsoft.public.dotnet.framework.adonet

Hi,

I'm executing the sql below several times, but one out of 10000... it throws
an exception! Any idea what could lead to this exception? I don't think it
has something to do with the WHERE statement. Should I try catch the
exception, then close and reopen the db, then try to execute the sql again?

Cannot execute sql: DELETE * FROM [l_sibvar] WHERE state_nbr>=1000000 AND
state_nbr<2000000 ---> System.Data.Odbc.OdbcException: ERROR [HY000]
[Microsoft][ODBC Microsoft Access Driver] The search key was not found in
any record



Thanks,

Sebastien

 

[Scot Rose [MSFT]]

Ok, Sorry I didn't notice before that this was Access<G>... Instead of using ODBC with Access, consider using the OLEDB Provider instead. It is more stable and performs better...
As far as the other errors, How many people do you have using the Access database at the same time (Using your program)? All of those errors (except maybe the Invalid
argument error) are common when using Access with too many people. If you have more than 10-15 (And there are those that will say this is too many) user connections (If you
have multiple connections open in the program multiply the number of connections by the number of people)with an Access database you really need to think about migrating to
SQL Server or some other database Server that is made for users. Access may let you use it with Multiple users but it is NOT a TRUE Multi User environment.

Want to know more? Check out the MSDN Library at http://msdn.microsoft.com or the Microsoft Knowledge Base at http://support.microsoft.com

Scot Rose, MCSD
Microsoft Visual Basic Developer Support
Email : (e-mail address removed) <Remove word online. from address>

This posting is provided “AS IS”, with no warranties, and confers no rights.




--------------------

From: "Sebastien Lange" <[email protected]>
References: <[email protected]> <[email protected]>
Subject: Re: Odbc exception - The search key was not found in any record
Date: Fri, 18 Jul 2003 09:22:30 +0200
Lines: 90
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <[email protected]>
Newsgroups: microsoft.public.dotnet.framework.adonet
NNTP-Posting-Host: alc243.alcatel.be 195.207.101.243
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:56085
X-Tomcat-NG: microsoft.public.dotnet.framework.adonet

Hi Scot,

The sql is executed in a long process, so I can't reproduce it. I'll now try
to close then reopen the connection, but I will not have the answer whether
it works or not, before another exception is reached by one of my users...
This is not the only strange problem I get with odbc; here are some other
exceptions occuring very rarely:
ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet
database engine stopped the process because you and another user are
attempting to change the same data at the same time.

ERROR [07009] [Microsoft][ODBC Microsoft Access Driver] Invalid argument.

ERROR [08004] [Microsoft][ODBC Microsoft Access Driver] Too many active
users.

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] Could not update;
currently locked.

System.InvalidOperationException: BeginTransaction requires an open and
available Connection. The connection's current state is Open, Executing.

Thanks,

Sebastien

Are you executing these one after the other in a loop or is this a longer

process. What happens if you Trap the exception, wait a few seconds and try
it again? Does a time delay

have any effect? Have you tried closing and reopening the connection and re-executing?

Want to know more? Check out the MSDN Library at http://msdn.microsoft.com

or the Microsoft Knowledge Base at http://support.microsoft.com

Scot Rose, MCSD
Microsoft Visual Basic Developer Support
Email : (e-mail address removed) <Remove word online. from address>

This posting is provided "AS IS", with no warranties, and confers no rights.

--------------------
From: "Sebastien Lange" <[email protected]>
Subject: Odbc exception - The search key was not found in any record
Date: Wed, 16 Jul 2003 15:49:48 +0200
Lines: 19
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <[email protected]>
Newsgroups: microsoft.public.dotnet.framework.adonet
NNTP-Posting-Host: alc246.alcatel.be 195.207.101.246
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:55869
X-Tomcat-NG: microsoft.public.dotnet.framework.adonet

Hi,

I'm executing the sql below several times, but one out of 10000... it throws
an exception! Any idea what could lead to this exception? I don't think it
has something to do with the WHERE statement. Should I try catch the
exception, then close and reopen the db, then try to execute the sql again?

Cannot execute sql: DELETE * FROM [l_sibvar] WHERE state_nbr>=1000000 AND
state_nbr<2000000 ---> System.Data.Odbc.OdbcException: ERROR [HY000]
[Microsoft][ODBC Microsoft Access Driver] The search key was not found in
any record



Thanks,

Sebastien

 

[Scot Rose [MSFT]]

If you can set up a small sample that reproduces the problem with the Northwind database I would be glad to take a look at it..

Want to know more? Check out the MSDN Library at http://msdn.microsoft.com or the Microsoft Knowledge Base at http://support.microsoft.com

Scot Rose, MCSD
Microsoft Visual Basic Developer Support
Email : (e-mail address removed) <Remove word online. from address>

This posting is provided “AS IS”, with no warranties, and confers no rights.




--------------------

From: "Sebastien Lange" <[email protected]>
References: <[email protected]> <[email protected]> <[email protected]> <ixr [email protected]>
Subject: Re: Odbc exception - The search key was not found in any record
Date: Fri, 18 Jul 2003 13:01:47 +0200
Lines: 169
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <[email protected]>
Newsgroups: microsoft.public.dotnet.framework.adonet
NNTP-Posting-Host: alc243.alcatel.be 195.207.101.243
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:56100
X-Tomcat-NG: microsoft.public.dotnet.framework.adonet

Thanks Scot for the quick reply.

Initially, I did some bench between oledb and odbc, and I choose odbc
because it was faster (.NET 1.0, now I'm using 1.1). I'll make a try with
oledb and redo my benchs.

I have only one person (and one connection) connected to the database,
that's why it is strange.
Sometimes several people can connect to the same database but it is rare and
I know when the errors occured, there was only 1 person connected.

Thanks,
Sebastien

Ok, Sorry I didn't notice before that this was Access<G>... Instead of

using ODBC with Access, consider using the OLEDB Provider instead. It is
more stable and performs better...

As far as the other errors, How many people do you have using the Access

database at the same time (Using your program)? All of those errors (except
maybe the Invalid

argument error) are common when using Access with too many people. If you

have more than 10-15 (And there are those that will say this is too many)
user connections (If you

have multiple connections open in the program multiply the number of

connections by the number of people)with an Access database you really need
to think about migrating to

SQL Server or some other database Server that is made for users. Access

may let you use it with Multiple users but it is NOT a TRUE Multi User
environment.

Want to know more? Check out the MSDN Library at http://msdn.microsoft.com

or the Microsoft Knowledge Base at http://support.microsoft.com

Scot Rose, MCSD
Microsoft Visual Basic Developer Support
Email : (e-mail address removed) <Remove word online. from address>

This posting is provided "AS IS", with no warranties, and confers no rights.

Subject: Re: Odbc exception - The search key was not found in any record
Date: Fri, 18 Jul 2003 09:22:30 +0200
Lines: 90
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <[email protected]>
Newsgroups: microsoft.public.dotnet.framework.adonet
NNTP-Posting-Host: alc243.alcatel.be 195.207.101.243
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:56085
X-Tomcat-NG: microsoft.public.dotnet.framework.adonet

Hi Scot,

The sql is executed in a long process, so I can't reproduce it. I'll now try
to close then reopen the connection, but I will not have the answer whether
it works or not, before another exception is reached by one of my users...
This is not the only strange problem I get with odbc; here are some other
exceptions occuring very rarely:
ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet
database engine stopped the process because you and another user are
attempting to change the same data at the same time.

ERROR [07009] [Microsoft][ODBC Microsoft Access Driver] Invalid argument.

ERROR [08004] [Microsoft][ODBC Microsoft Access Driver] Too many active
users.

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] Could not update;
currently locked.

System.InvalidOperationException: BeginTransaction requires an open and
available Connection. The connection's current state is Open, Executing.

Thanks,

Sebastien

Are you executing these one after the other in a loop or is this a longer
process. What happens if you Trap the exception, wait a few seconds and try
it again? Does a time delay
have any effect? Have you tried closing and reopening the connection and
re-executing?

Want to know more? Check out the MSDN Library at http://msdn.microsoft.com
or the Microsoft Knowledge Base at http://support.microsoft.com

Scot Rose, MCSD
Microsoft Visual Basic Developer Support
Email : (e-mail address removed) <Remove word online. from address>

This posting is provided "AS IS", with no warranties, and confers no
rights.




--------------------
From: "Sebastien Lange" <[email protected]>
Subject: Odbc exception - The search key was not found in any record
Date: Wed, 16 Jul 2003 15:49:48 +0200
Lines: 19
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <[email protected]>
Newsgroups: microsoft.public.dotnet.framework.adonet
NNTP-Posting-Host: alc246.alcatel.be 195.207.101.246
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:55869
X-Tomcat-NG: microsoft.public.dotnet.framework.adonet

Hi,

I'm executing the sql below several times, but one out of 10000... it
throws
an exception! Any idea what could lead to this exception? I don't think
it
has something to do with the WHERE statement. Should I try catch the
exception, then close and reopen the db, then try to execute the sql
again?

Cannot execute sql: DELETE * FROM [l_sibvar] WHERE state_nbr>=1000000 AND
state_nbr<2000000 ---> System.Data.Odbc.OdbcException: ERROR [HY000]
[Microsoft][ODBC Microsoft Access Driver] The search key was not found in
any record



Thanks,

Sebastien