OleDbException

  • Thread starter Thread starter 00unknown.user00
  • Start date Start date
0

00unknown.user00

Is there any way to determine the cause of an OleDbException? For
instance. for "No value given for one or more required parameters." is
there a way to determine which parameter is missing? Or for the "Data
type mismatch in criteria expression", to determine which parameter is
causing the problem?
I'm ripping my hair out trying to track these things down.
The queries generally work perfectly fine when used directly in Access,
but when executed through my code, they fail.

Here's an example:

private string insertCommandString = @"INSERT INTO AppUser(Id,
Username, Passphrase, Permissions, Creator_Id) VALUES (@Id, @Username,
@Passphrase, @Permissions, @Creator_Id)";

OleDbCommand insertCmd = new OleDbCommand( insertCommandString );
insertCmd.Parameters.Add( "@Id", item.Id );
insertCmd.Parameters.Add( "@Username", item.Username );
insertCmd.Parameters.Add( "@Passphrase", item.Passphrase );
insertCmd.Parameters.Add( "@Permissions", item.Permissions );
insertCmd.Parameters.Add( "@Creator_Id", appuser_Id );

using ( OleDbConnection conn = new OleDbConnection(_connectionString )
)
{
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();
}

This generates a "No value given for one or more required parameters."
exception. But when I create a query: "INSERT INTO AppUser(Id,
Username, Passphrase, Permissions, Creator_Id) VALUES (@Id, @Username,
@Passphrase, @Permissions, @Creator_Id)" and execute it via Access, it
asks me for the parameters correctly.

This is driving me insane, I've got 20 different classes and each one
ends up having these problems.
 
Is there any way to determine the cause of an OleDbException? For
instance. for "No value given for one or more required parameters." is
there a way to determine which parameter is missing? Or for the "Data
type mismatch in criteria expression", to determine which parameter is
causing the problem?
I'm ripping my hair out trying to track these things down.
The queries generally work perfectly fine when used directly in Access,
but when executed through my code, they fail.

Here's an example:

private string insertCommandString = @"INSERT INTO AppUser(Id,
Username, Passphrase, Permissions, Creator_Id) VALUES (@Id, @Username,
@Passphrase, @Permissions, @Creator_Id)";

OleDbCommand insertCmd = new OleDbCommand( insertCommandString );
insertCmd.Parameters.Add( "@Id", item.Id );
insertCmd.Parameters.Add( "@Username", item.Username );
insertCmd.Parameters.Add( "@Passphrase", item.Passphrase );
insertCmd.Parameters.Add( "@Permissions", item.Permissions );
insertCmd.Parameters.Add( "@Creator_Id", appuser_Id );

using ( OleDbConnection conn = new OleDbConnection(_connectionString )
)
{
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();
}

This generates a "No value given for one or more required parameters."
exception. But when I create a query: "INSERT INTO AppUser(Id,
Username, Passphrase, Permissions, Creator_Id) VALUES (@Id, @Username,
@Passphrase, @Permissions, @Creator_Id)" and execute it via Access, it
asks me for the parameters correctly.

This is driving me insane, I've got 20 different classes and each one
ends up having these problems.
Click to expand...
Although it appears you have properly added your parameters, I see no
place in your code where you set the values of the parameters.

To answer your question, though:

If you want to determine where an error is occurring you should trap
the error in a try.catch block:

try
{
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();
}
catch(OleDbException ex)
{
// shows all the information
MessageBox.Show(this, ex.ToString());
// or shows the stack trace
MessageBox.Show(this, ex.StackTrace);
}

Otis Mukinfus
http://www.otismukinfus.com
http://www.tomchilders.com
 
All of these problems occur in try{}catch{} blocks. The exceptions I
mentioned are caught within within them. The problem is disagnosising
and figuring out what causes these exceptions.
The example I gave does set the parameters:
insertCmd.Parameters.Add( "@Id", item.Id );
sets the parameter @Id to the value of item.Id, maybe not as efficently
as
insertCmd.Parameters.Add( "@Id", OleDbType.Decimal).Value = item.Id;
but it still works (OleDbType.Decimal is required because I'm using
Int64's for ids, and Access's long int is only 32 bits!!! Access's
Decimal type is the only type that seems to hold c# longs)
 
hello 00unknown,
in cases like this first is you have to know the data type mapping
used by ado.net. I am pertainig to those a table where every sql types
has corresponding oledbdatatypes.

also, break points is our long time friend. stepping thru each lines of
code makes use suspect and investigates codes that causing the error.
Also stack trace will give you specidic lines that causes the error.

regards,
rodel e. dagumamapan
http://community.devpinoy.org/blogs/dehranph
 
Stepping though the code does no good. The problems always occur on
the ExecuteNonQuery or ExecuteReader lines. No errors occur when
setting the parameters.
 
hello,

also on your business entities, make sure you have set values there.

when you use break points together with a try-catch block, it will
surely gives you singnifacnt error information. I have no idea why you
can't catch them.

Press F10, to scan codes line by line.

regards,
rodel e. dagumamapan
http://community.devpinoy.org/blogs/dehranph
 
I don't understand. The error is thrown from 1 line only. The
ExecuteNonQuery line. There is no code to step into. The error is
always the same Datatype mismatch in criteria expression. Where is
there additional information (such as which parameter is causing the
problem?)
 
Back
Top