Storing Number in an integer from sql Database

Question

i am using database with table RESUME and column PageIndex in it which type is number in database but when i want to store this PageIndex value to an integer i get exception error

Specified cast is not valid.

here is the code

string sql;
string conString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\\Deliverable4.accdb";
protected OleDbConnection rMSConnection;
protected OleDbDataAdapter rMSDataAdapter;
protected DataSet dataSet;
protected DataTable dataTable;
protected DataRow dataRow;

on Button Click

sql = "select PageIndex from RESUME";
rMSConnection = new OleDbConnection(conString);
rMSDataAdapter = new OleDbDataAdapter(sql, rMSConnection);
dataSet = new DataSet("pInDex");
rMSDataAdapter.Fill(dataSet, "RESUME");

dataTable = dataSet.Tables["RESUME"];

int pIndex = (int)dataTable.Rows[0][0];

rMSConnection.Close();

if (pIndex == 0)
{            
    Response.Redirect("Create Resume-1.aspx");
}
else if (pIndex == 1)
{            
    Response.Redirect("Create Resume-2.aspx");
}
else if (pIndex == 2)
{          
        Response.Redirect("Create Resume-3.aspx");
    }
}

i am getting error in this line

int pIndex = (int)dataTable.Rows[0][0];

Adam Robinson · Accepted Answer · 2010-06-14 17:19:38Z

2

If it can't cast it to an integer, then it isn't retrieving it as one from the database. What is the data type of the field?

Try examining the results of dataTable.Rows[0][0].GetType().ToString() to see what the object actually is.

answered Jun 14, 2010 at 17:19

Adam Robinson

186k36 gold badges294 silver badges351 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marc Gravell · Accepted Answer · 2010-06-14 17:21:28Z

2

You say "type is number in database" - it sounds as though this might be numeric, in which case the most appropriate match in .NET-land is decimal. If you know it is an int, you can simply cast it from decimal to int afterwards:

int pIndex = (int)(decimal)dataTable.Rows[0][0];

answered Jun 14, 2010 at 17:21

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Comments

stakx - no longer contributing · Accepted Answer · 2010-06-15 20:23:35Z

0

May I very humbly suggest a different way of doing this?:

string conString = "...";  // <-- your connection string

using (IDbConnection connection = new OleDbConnection(conString))
{   // ^^^ interface type makes your code less dependent on a particular backend!
    connection.Open();
    try
    {
        using (IDbCommand command = connection.CreateCommand())
        {   // ^^^ ditto
            command.CommandText = "SELECT PageIndex FROM RESUME";
            object scalar = command.ExecuteScalar();
            // ^ ExecuteScalar reads the first value in the first column
            //   of the result set; or returns null if it fails to do so.

            if (scalar == null) throw ...;  // unexpected result from database!
            if (scalar == DBNull.Value) throw ...;  // ditto!?

            int pIndex = (int)scalar;

            switch (pIndex)
            {
                case 0:  Response.Redirect("Create Resume-1.aspx"); break;
                case 1:  Response.Redirect("Create Resume-2.aspx"); break;
                case 2:  Response.Redirect("Create Resume-3.aspx"); break;
                default: throw ...;  // unexpected result from database!?
            }
        }
    }
    finally
    {
        connection.Close();  // (will execute even when exceptions are thrown!)
    }
}

edited Jun 15, 2010 at 20:23

answered Jun 14, 2010 at 18:31

stakx - no longer contributing

85.1k20 gold badges176 silver badges277 bronze badges

2 Comments

Adam Robinson Over a year ago

A null database value will produce DBNull.Value, not null.

stakx - no longer contributing Over a year ago

@Adam Robinson: Correct (I added this case), but note that null can still occur if there isn't a first column of a first row, ie. when the returned result set is completely empty.

Taryn · Accepted Answer · 2013-03-05 02:21:24Z

0

I solved the problem.

int pIndex = int.Parse(dataTable.Rows[0][0].ToString());

edited Mar 5, 2013 at 2:21

Taryn

249k57 gold badges375 silver badges409 bronze badges

answered Jun 14, 2010 at 18:16

ar31an

2635 silver badges9 bronze badges

Collectives™ on Stack Overflow

Storing Number in an integer from sql Database

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related