OleDb ADO.Net ExecuteScalar()

  • Thread starter Thread starter WeiminZhang
  • Start date Start date
W

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;database=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.
 
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,
 
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.
 
Back
Top