OleDb ADO.Net ExecuteScalar()

[WeiminZhang]

When I use the OleDb to connect a Oracle db, and use the
ExecutScalar() method to get the count of a table, the
return value can't be cast to a data type, say int, while
this works fine for a SQL server db using SqlCommand.

Following is the code example:

string strCnt = "SELECT count (*) from B_PlanSchemeItems";
string strSource="Provider=MSDAORA;Data
Source=avocet1;User ID=dbo; Password=mmsmms;";
OleDbConnection conn = new OleDbConnection(strSource);
conn.Open();

OleDbCommand cmdCount = new OleDbCommand(strCnt,conn);
object objInt;
objInt = cmdCount.ExecuteScalar();
intNumRecs = (int) (objInt);

The run time error occurs with message:
"Specified cast is not valid"

Following code works fine for a SQL server db:

string strCnt = "SELECT count (*) from B_PlanSchemeItems";
string
strSource="server=planserv1;uid=sa;pwd=sysadmin;da tabase=a
vocet1";
SqlConnection myConn = new SqlConnection(strSource);
myConn.Open();

SqlCommand cmdCount = new SqlCommand(strCnt, myConn);
intNumRecs = (int) cmdCount.ExecuteScalar();

Can anyone help me on solving the cast problem in OleDb
case? Thanks.

[Ignacio Machin]

Hi ,

OleDbCommand.ExecuteScalar return an object , not in integer so maybe what
you are getting is a Decimal and therefore the cast is not valid, this is
what I would do.
Assign it to a object variable as you do:
objInt = cmdCount.ExecuteScalar();
place a breakpoint on this line and see what is the correct type, just to
know ;)
I would use Convert.ToInt32() instead of cast:
intNumRecs =Convert.ToInt32( objInt);

this should work fine.

Hope this help,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"WeiminZhang" <wz****@stssystems.com> wrote in message
news:01****************************@phx.gbl...

When I use the OleDb to connect a Oracle db, and use the
ExecutScalar() method to get the count of a table, the
return value can't be cast to a data type, say int, while
this works fine for a SQL server db using SqlCommand.

Following is the code example:

string strCnt = "SELECT count (*) from B_PlanSchemeItems";
string strSource="Provider=MSDAORA;Data
Source=avocet1;User ID=dbo; Password=mmsmms;";
OleDbConnection conn = new OleDbConnection(strSource);
conn.Open();

OleDbCommand cmdCount = new OleDbCommand(strCnt,conn);
object objInt;
objInt = cmdCount.ExecuteScalar();
intNumRecs = (int) (objInt);

The run time error occurs with message:
"Specified cast is not valid"

Following code works fine for a SQL server db:

string strCnt = "SELECT count (*) from B_PlanSchemeItems";
string
strSource="server=planserv1;uid=sa;pwd=sysadmin;da tabase=a
vocet1";
SqlConnection myConn = new SqlConnection(strSource);
myConn.Open();

SqlCommand cmdCount = new SqlCommand(strCnt, myConn);
intNumRecs = (int) cmdCount.ExecuteScalar();

Can anyone help me on solving the cast problem in OleDb
case? Thanks.

[Weimin Zhang]

Thanks, Ignacio Machin!

It works fine now. Looks like the SqlClient object is not
exactly same as OleDb in the way it returns the object.

Anyway, thanks again.

-----Original Message-----
Hi ,

OleDbCommand.ExecuteScalar return an object , not in integer so maybe whatyou are getting is a Decimal and therefore the cast is not valid, this iswhat I would do.
Assign it to a object variable as you do:
objInt = cmdCount.ExecuteScalar();
place a breakpoint on this line and see what is the correct type, just toknow ;)
I would use Convert.ToInt32() instead of cast:
intNumRecs =Convert.ToInt32( objInt);

this should work fine.

Hope this help,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"WeiminZhang" <wz****@stssystems.com> wrote in message
news:01****************************@phx.gbl...

When I use the OleDb to connect a Oracle db, and use the ExecutScalar() method to get the count of a table, the
return value can't be cast to a data type, say int, while this works fine for a SQL server db using SqlCommand.

Following is the code example:

string strCnt = "SELECT count (*) from B_PlanSchemeItems"; string strSource="Provider=MSDAORA;Data
Source=avocet1;User ID=dbo; Password=mmsmms;";
OleDbConnection conn = new OleDbConnection(strSource);
conn.Open();

OleDbCommand cmdCount = new OleDbCommand(strCnt,conn);
object objInt;
objInt = cmdCount.ExecuteScalar();
intNumRecs = (int) (objInt);

The run time error occurs with message:
"Specified cast is not valid"

Following code works fine for a SQL server db:

string strCnt = "SELECT count (*) from B_PlanSchemeItems"; string
strSource="server=planserv1;uid=sa;pwd=sysadmin;da tabase=a vocet1";
SqlConnection myConn = new SqlConnection(strSource);
myConn.Open();

SqlCommand cmdCount = new SqlCommand(strCnt, myConn);
intNumRecs = (int) cmdCount.ExecuteScalar();

Can anyone help me on solving the cast problem in OleDb
case? Thanks.

.