Autonumber and datatable with dbnull exception

Question

i was doing some work on a datatable i filled with a oledbdataadapter made from an access database. and i stumbled upon this error:

Turns out that my table has this structure: ID --> autonumber(PK)

lazos_>text

Asociaciones->text

and when i fill my datatable all values pass to it without any problems with all the correct values. I insert a new row like shown on the "insert row" part.

i do this asumming that my pk will instert the "autonumber" on row creation, but apparently it is not doing it because when i loop trought the rows i get a "invalid cast exception" with a Object cannot be cast from DBNull to other types."

I COULD insert an id value to the column, but when i update my dt to my database wont it create an error, because i have no way of knowing wich was the last row created?, or do i?

for example lets say in my datatable the last ID is 50, but on the database y previously made a record with id "51" but then erased it, if i inserted 51 based on my dt info, it would give an error right?

    //// INSERT ROW
    DataRow newRow = Tabla_Cods_Proy.NewRow();
    newRow["Lazos"] = textBox1.Text ;
    newRow["Asociaciones"] = textBox2.Text;
    Tabla_Cods_Proy.Rows.Add(newRow);
    MessageBox.Show("Enhorabuena!");


//CHECK ID's            
    for (int i = 0; i < Tabla_Cods_Proy.Rows.Count; i++)
    {
        if (Tabla_Cods_Proy.Rows[i].RowState != DataRowState.Deleted)
        {
            if (Tabla_Cods_Proy.Rows[i]["Lazos_asociados"].ToString() == "")
            {

                listBox7.Items.Add(Tabla_Cods_Proy.Rows[i]["Cod_Cliente"]);
                listBox8.Items.Add(Tabla_Cods_Proy.Rows[i]["Cod_Inelectra"]);
                ID_Cods_Proy_Sin_Asociar.Add(Convert.ToInt32(Tabla_Cods_Proy.Rows[i]["ID"]));

            }
            else
            {
                listBox3.Items.Add(Tabla_Cods_Proy.Rows[i]["Cod_Cliente"]);
                listBox4.Items.Add(Tabla_Cods_Proy.Rows[i]["Cod_Inelectra"]);
                ID_Cods_Proy_Asociados.Add(Convert.ToInt32(Tabla_Cods_Proy.Rows[i]["ID"]));
            }
        }

score 0 · Answer 1 · answered Sep 03 '11 at 16:54

I had once similiar problem. What you need to do is that you retrieve the new identity @@IDENTITY of this column once you insert it into table. You can do that by using RowUpdated event.

Here is quick example from MSDN page (similiar to your case, see bottom of the page):

    public static void Main() 
    {
       //...connecting to access db and getting data to datatable...
       // ...
       // Adding a new row to datatable.
       DataRow newRow = catDS.Tables["Categories"].NewRow();
       newRow["CategoryName"] = "New Category";
       catDS.Tables["Categories"].Rows.Add(newRow);

       // Include an event to fill in the Autonumber value.
       catDA.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated);

    }

    protected static void OnRowUpdated(object sender, OleDbRowUpdatedEventArgs args)
    {
       // Include a variable and a command to retrieve the identity value from the Access database.
       int newID = 0;
       OleDbCommand idCMD = new OleDbCommand("SELECT @@IDENTITY", nwindConn);

       if (args.StatementType == StatementType.Insert)
       {
          // Retrieve the identity value and store it in the CategoryID column.
          newID = (int)idCMD.ExecuteScalar();
          args.Row["CategoryID"] = newID;
       }
    }

Autonumber and datatable with dbnull exception

1 Answers1