returning numeric values from access database

  • Thread starter Thread starter James Jenkins
  • Start date Start date
J

James Jenkins

Hi - I am developing a database class which accesses an Access database to
simply retrieve decimal values. When I call the code below it fails with an
'
Unspecified error' - The database it calls have the 2 columns (BEGINDEC &
ENDDEC) and both are set to data type 'Double' in the database. I have also
tried this code in varying forms but always with different error messages -
what is my best option to return Double values from an Access Database -

James...

double ipdec = GetAddress(Address);

OleDbConnection conn = new OleDbConnection(@"Data
Source=GIPS.mdb;Password=;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin");

try

{

string sql = "SELECT COUNTRY FROM GEOS WHERE BEGINDEC >= ";

sql += ipdec;

sql += " AND ENDDEC <= ";

sql += ipdec.ToString();

conn.Open();

OleDbDataAdapter adapt = new OleDbDataAdapter(sql,conn);

DataSet ds = new DataSet();


adapt.Fill(ds);


foreach(DataRow dr in ds.Tables[0].Rows)

{

Debug.WriteLine(dr["COUNTRY"]);

}catch{exception ex)Debug,Writeline(ex.Message));
 
A few things:

1) Unspecified Error usually has an InnerException with more detailed
information. Look there.
2) What line of code is actually throwing the error?
3) ipdec is a double, yet you're adding it to a string (sql += ipdec;).
Shouldn't it be (sql += ipdec.ToString();)?
4) Strings are immutable, so you want to change them as infrequently as
possible. Try replacing your sql code with:

string sql = "SELECT COUNTRY FROM GEOS WHERE BEGINDEC >= " +
ipdec.ToString() +
" AND ENDDEC <= " +
ipdec.ToString();

It's just as readable and a lot more efficient.


--ROBERT
 
Robert Bouillon said:
A few things:

1) Unspecified Error usually has an InnerException with more detailed
information. Look there.
2) What line of code is actually throwing the error?
3) ipdec is a double, yet you're adding it to a string (sql += ipdec;).
Shouldn't it be (sql += ipdec.ToString();)?
4) Strings are immutable, so you want to change them as infrequently as
possible. Try replacing your sql code with:

string sql = "SELECT COUNTRY FROM GEOS WHERE BEGINDEC >= " +
ipdec.ToString() +
" AND ENDDEC <= " +
ipdec.ToString();

It's just as readable and a lot more efficient.


--ROBERT

James Jenkins said:
Hi - I am developing a database class which accesses an Access database
to
simply retrieve decimal values. When I call the code below it fails with an
'
Unspecified error' - The database it calls have the 2 columns (BEGINDEC &
ENDDEC) and both are set to data type 'Double' in the database. I have also
tried this code in varying forms but always with different error messages -
what is my best option to return Double values from an Access Database -

James...

double ipdec = GetAddress(Address);

OleDbConnection conn = new OleDbConnection(@"Data
Source=GIPS.mdb;Password=;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin");

try

{

string sql = "SELECT COUNTRY FROM GEOS WHERE BEGINDEC >= ";

sql += ipdec;

sql += " AND ENDDEC <= ";

sql += ipdec.ToString();

conn.Open();

OleDbDataAdapter adapt = new OleDbDataAdapter(sql,conn);

DataSet ds = new DataSet();


adapt.Fill(ds);


foreach(DataRow dr in ds.Tables[0].Rows)

{

Debug.WriteLine(dr["COUNTRY"]);

}catch{exception ex)Debug,Writeline(ex.Message));
Click to expand...
Click to expand...

thanks for your tips and help - but when I use this sql statement it returns
'
No value given for one or more required parameters' - when filling the
adapter - I am wondering if there is another problem here such as the
database or maybe I need to use another method - thanks
 
Not sure what's causing that. Can you post what the sql string is right
before it's executed?

Keep in Mind the DataSet is very overrated. Unless you need all of the
functionality of the DataSet, try using a DataReader instead (Code Below).

http://msdn.microsoft.com/msdnmag/issues/04/06/DataPoints/default.aspx

In a nutshell, the DataAdapter/DataSet pair is much more resource intensive
than the DataReader.

Note the Finally I used. Always clean up DB resources in a finally clause.
If your code breaks because it's missing a catch statment, always use PLAY
rather than STOP in debugging to ensure code in the Finally clause is
executed.



double ipdec = GetAddress(Address);
OleDbConnection conn = null;
IDbCommand cmd = null;
IDataReader idr = null;

try
{
conn = new OleDbConnection(@"Data
Source=GIPS.mdb;Password=;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin");

conn.Open();
cmd = conn.CreateCommand();
cmd.CommandText = "SELECT COUNTRY FROM GEOS WHERE BEGINDEC >= " +
ipdec.ToString() +
" AND ENDDEC <= " +
ipdec.ToString();

idr = cmd.ExecuteReader();

while(idr.read())
{
Debug.WriteLine(idr["COUNTRY"]);
}
}
catch(exception ex)
{
Debug,Writeline(ex.Message));
}
finally
{
if(idr!=null)
{
if(!idr.IsClosed)
idr.Close();
idr.Dispose();
}
if(cmd!=null)
cmd.Dispose();
if(conn!=null)
{
if(conn.State = ConnectionState.Open)
conn.Close();
conn.Dispose();
}
}




James Jenkins said:
Robert Bouillon said:
A few things:

1) Unspecified Error usually has an InnerException with more detailed
information. Look there.
2) What line of code is actually throwing the error?
3) ipdec is a double, yet you're adding it to a string (sql += ipdec;).
Shouldn't it be (sql += ipdec.ToString();)?
4) Strings are immutable, so you want to change them as infrequently as
possible. Try replacing your sql code with:

string sql = "SELECT COUNTRY FROM GEOS WHERE BEGINDEC >= " +
ipdec.ToString() +
" AND ENDDEC <= " +
ipdec.ToString();

It's just as readable and a lot more efficient.


--ROBERT

Hi - I am developing a database class which accesses an Access database
to
simply retrieve decimal values. When I call the code below it fails
Click to expand...
with
an
'
Unspecified error' - The database it calls have the 2 columns (BEGINDEC &
ENDDEC) and both are set to data type 'Double' in the database. I have also
tried this code in varying forms but always with different error messages -
what is my best option to return Double values from an Access Database -

James...

double ipdec = GetAddress(Address);

OleDbConnection conn = new OleDbConnection(@"Data
Source=GIPS.mdb;Password=;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin");

try

{

string sql = "SELECT COUNTRY FROM GEOS WHERE BEGINDEC >= ";

sql += ipdec;

sql += " AND ENDDEC <= ";

sql += ipdec.ToString();

conn.Open();

OleDbDataAdapter adapt = new OleDbDataAdapter(sql,conn);

DataSet ds = new DataSet();


adapt.Fill(ds);


foreach(DataRow dr in ds.Tables[0].Rows)

{

Debug.WriteLine(dr["COUNTRY"]);

}catch{exception ex)Debug,Writeline(ex.Message));
Click to expand...
Click to expand...

thanks for your tips and help - but when I use this sql statement it returns
'
No value given for one or more required parameters' - when filling the
adapter - I am wondering if there is another problem here such as the
database or maybe I need to use another method - thanks
Click to expand...
 
Back
Top