Using OleDBParameter in a simple UPDATE statement

[Patrick Questembert]

I am afraid I am not understanding at all how OleDbParameters work (I am new
to SQL) ... the simple code below fails with E_ABORT:

OleDbParameter p = catCMD.CreateParameter();
p.ParameterName = "val2";
p.OleDbType = OleDbType.Integer;
p.Value = 8;
catCMD.Parameters.Add(p);
catCMD.CommandText = "INSERT INTO MyTable (val1,val2) VALUES (5,?);"
catCMD.ExecuteNonQuery();

[Not sure what's the role of the ParameterName actually, I would have
thought that all that matters is the position of the parameter in the params
collection, as matching the "?" I am targetting.]

Help !

Patrick

PS: this is with MySQL 4.1 as the database

 

[W.G. Ryan eMVP]

Is this the whole code block ie do you have an open connection available to
run this on?

 

[Sahil Malik]

Position is not all that matters, the name matters too - particularly for
stored procs. Actually that is specific to the underlying database you are
using.

Anyway, another thing databases are rather picky about at times are the
specific data type.

Even the .NET common types donot map one on one to Sql types, and that's a
pain on the booty 9 times out of 10, but a simple data mapping function gets
over that. Also you might already know this, but an int to one platform
might be not an int some other platform (# of bits might differ).

- Sahil Malik
You can reach me thru my blog -
http://www.dotnetjunkies.com/weblog/sahilmalik

 

[Patrick Questembert]

I do have an open connection - in fact other queries (not using parameters)
using the same connection work fine.

The value in the table is "SMALLINT(5)" - so besides OleDbType.Integer, I
also tried SmallInt and TinyInt - to no avail, same E_ABORT error.

Could this have anything to do with the OleDb MySQL provider I am using
(dating from 2/2001) and if so, which one would you recommend instead?

Thanks!
Patrick

Is this the whole code block ie do you have an open connection available
to
run this on?



--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

I am afraid I am not understanding at all how OleDbParameters work (I am new
to SQL) ... the simple code below fails with E_ABORT:

OleDbParameter p = catCMD.CreateParameter();
p.ParameterName = "val2";
p.OleDbType = OleDbType.Integer;
p.Value = 8;
catCMD.Parameters.Add(p);
catCMD.CommandText = "INSERT INTO MyTable (val1,val2) VALUES (5,?);"
catCMD.ExecuteNonQuery();

[Not sure what's the role of the ParameterName actually, I would have
thought that all that matters is the position of the parameter in the params
collection, as matching the "?" I am targetting.]

Help !

Patrick

PS: this is with MySQL 4.1 as the database

 

[Patrick Questembert]

When I do: "DESCRIBE MyTable;" it shows the column I am targetting as being
of the type "INT(11)" (I guess 11 means MySQL needs 11 bytes to store it).
Furthermore, when I do a "SELECT val2 FROM MyTable;" and print out the type
of the first column (myReader[0].GetType()), I get "System.Int32".

This is supposed to map to OleDbType.Integer - which fails.
Just in case, I also tried OleDbType.SmallInt and OleDbType.BigInt.

Nada - all yield the illuminating E_ABORT error ...

Am I expecting too much to be able to read and update a simple signed
integer value ?
And I think some kind of useful hint(s) in the error return would have been
nice.

Here is again the minimal code showing the error:


DatabaseConnection = new OleDbConnection("Provider=MySQLProv;Data
Source=WebReader; User ID=root;");
DatabaseConnection.Open();
OleDbParameter p = catCMD.CreateParameter();
p.ParameterName = "val2";
p.OleDbType = OleDbType.Integer;
p.Value = System.Convert.ToInt32(123);
// I assume no need to set p.Size
catCMD.Parameters.Add(p);
catCMD.CommandText = "INSERT INTO t2 (val1,val2) VALUES (8,?);";
catCMD.ExecuteNonQuery();

Any ideas?

By the way: I just spend a couple of hours until I figured out that
apparently the SQL commands via the .NET OleDb objects w/MySQL are required
to have the reserved keywords in uppercase ... needs to be "SELECT etc", not
"select etc" (which generates an exception, with the same E_ABORT code). I
certainly didn't expect nor read anything about it anywhere, but apparently
it is so ...

Thanks,
Patrick

Position is not all that matters, the name matters too - particularly for
stored procs. Actually that is specific to the underlying database you are
using.

Anyway, another thing databases are rather picky about at times are the
specific data type.

Even the .NET common types donot map one on one to Sql types, and that's a
pain on the booty 9 times out of 10, but a simple data mapping function
gets over that. Also you might already know this, but an int to one
platform might be not an int some other platform (# of bits might differ).

- Sahil Malik
You can reach me thru my blog -
http://www.dotnetjunkies.com/weblog/sahilmalik

I am afraid I am not understanding at all how OleDbParameters work (I am
new to SQL) ... the simple code below fails with E_ABORT:

OleDbParameter p = catCMD.CreateParameter();
p.ParameterName = "val2";
p.OleDbType = OleDbType.Integer;
p.Value = 8;
catCMD.Parameters.Add(p);
catCMD.CommandText = "INSERT INTO MyTable (val1,val2) VALUES (5,?);"
catCMD.ExecuteNonQuery();

[Not sure what's the role of the ParameterName actually, I would have
thought that all that matters is the position of the parameter in the
params collection, as matching the "?" I am targetting.]

Help !

Patrick

PS: this is with MySQL 4.1 as the database

 

[Sahil Malik]

In regards to data types, Well, ado.net strangely is not as picky about
filling data in a dataset, as it is in regards to using parameters etc in
ado.net commands, especially stored procs. And the useful hints - well, hard
to come up with a useful hint in OleDb which is one size fits all. (I don't
know but I am trying to stand in M$'s shoes).

The reserved words portion (SELECT vs. select) is news to me too, but I
hadn't worked with MySql before. Did you check out sourceforge.net for a
dataprovider for MySql? I think there is one, and that might ease your pains
a bit. Or of course there is MSDE.

- Sahil Malik
You can reach me thru my blog http://www.dotnetjunkies.com/weblog/sahilmalik

When I do: "DESCRIBE MyTable;" it shows the column I am targetting as
being of the type "INT(11)" (I guess 11 means MySQL needs 11 bytes to
store it). Furthermore, when I do a "SELECT val2 FROM MyTable;" and print
out the type of the first column (myReader[0].GetType()), I get
"System.Int32".

This is supposed to map to OleDbType.Integer - which fails.
Just in case, I also tried OleDbType.SmallInt and OleDbType.BigInt.

Nada - all yield the illuminating E_ABORT error ...

Am I expecting too much to be able to read and update a simple signed
integer value ?
And I think some kind of useful hint(s) in the error return would have
been nice.

Here is again the minimal code showing the error:


DatabaseConnection = new OleDbConnection("Provider=MySQLProv;Data
Source=WebReader; User ID=root;");
DatabaseConnection.Open();
OleDbParameter p = catCMD.CreateParameter();
p.ParameterName = "val2";
p.OleDbType = OleDbType.Integer;
p.Value = System.Convert.ToInt32(123);
// I assume no need to set p.Size
catCMD.Parameters.Add(p);
catCMD.CommandText = "INSERT INTO t2 (val1,val2) VALUES (8,?);";
catCMD.ExecuteNonQuery();

Any ideas?

By the way: I just spend a couple of hours until I figured out that
apparently the SQL commands via the .NET OleDb objects w/MySQL are
required to have the reserved keywords in uppercase ... needs to be
"SELECT etc", not "select etc" (which generates an exception, with the
same E_ABORT code). I certainly didn't expect nor read anything about it
anywhere, but apparently it is so ...

Thanks,
Patrick

Position is not all that matters, the name matters too - particularly for
stored procs. Actually that is specific to the underlying database you
are using.

Anyway, another thing databases are rather picky about at times are the
specific data type.

Even the .NET common types donot map one on one to Sql types, and that's
a pain on the booty 9 times out of 10, but a simple data mapping function
gets over that. Also you might already know this, but an int to one
platform might be not an int some other platform (# of bits might
differ).

- Sahil Malik
You can reach me thru my blog -
http://www.dotnetjunkies.com/weblog/sahilmalik

I am afraid I am not understanding at all how OleDbParameters work (I am
new to SQL) ... the simple code below fails with E_ABORT:

OleDbParameter p = catCMD.CreateParameter();
p.ParameterName = "val2";
p.OleDbType = OleDbType.Integer;
p.Value = 8;
catCMD.Parameters.Add(p);
catCMD.CommandText = "INSERT INTO MyTable (val1,val2) VALUES (5,?);"
catCMD.ExecuteNonQuery();

[Not sure what's the role of the ParameterName actually, I would have
thought that all that matters is the position of the parameter in the
params collection, as matching the "?" I am targetting.]

Help !

Patrick

PS: this is with MySQL 4.1 as the database