Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

Can't update database from dataset

Hi.

I have an access db and a couple of routines using a odbcDataAdapter, and a generated data set.

I want to add a new row to the database, so my code looks like this

nr = new row, ds = my dataset, and the function _DataRowUpdate has some code that fills data to the new row (for e.g. nr["EntryDate"] = DateTime.Now etc.

===
DataRow nr = _ds.Tables[1].NewRow();
            this._DataRowUpdate(nr,w);
            _ds.Tables[1].Rows.Add(nr);
            nr.AcceptChanges();           
         
            try
            {
               
                w.DataRowEntry = nr;               
                _ds.AcceptChanges();
                _oDA.Update(_ds);                                     
            }
            catch ( Exception ex )
            {
                MessageBox.Show(ex.ToString());
            }
            this._oDC.Close(); 
===

Just doesn't work! no exceptions, but no new row either :( I'm really lost. Someone please tell me what's wrong (on the other hand, a similar routine where I get the nr, and then do nr.Delete() seems to work)

P.S. I tried using the acceptchanges AFTER the _oDA.update as well.

dblost
Saturday, March 05, 2005

Try removing the nr.AcceptChanges() as well.

Joe
Saturday, March 05, 2005

I mean ... as well as moving acceptchanges AFTER the _oDA.update

Joe
Saturday, March 05, 2005

Joe, Thank you very much! it worked.

Looks like now i'm into another trouble, I have a similar function to update

public bool UpdateRecord(wwdb w)
        {   
            DataRow nr = _ds.Tables[1].Rows.Find(w.Index);
            this._DataRowUpdate(nr,w);                   
                                         
            try
            {               
                _oDA.Update(_ds);
                _ds.AcceptChanges();
            }           
            catch (Exception e)
            {
                  MessageBox.Show(e.ToString(),"OOPS! something went wrong..");
            }
            return true;
        }

But now this one throws up a "data type mismatch in criteria expression " error :( and does not update the row. What am I missing? Sorry if I'm being naive, I'm very new to .NET and stuff...

dblost
Saturday, March 05, 2005

Difficult to say without seeing more code, also it would help to know at which line in your code this exception is being thrown.

But I'll take a guess: the Find method you are using will require that you pass an object (or array of objects) with the same type as the primary key column(s) in your DataTable.

- Have you defined a primary key in your table?
- If so, presumably the PK has only one column?
- What is the data type of your primary key columns and is it the same as the data type of w.Index?

Joe
Saturday, March 05, 2005

Joe,
My comments  below,

Difficult to say without seeing more code, also it would help to know at which line in your code this exception is being thrown.

>>the line where _oDAupdate(_ds);

But I'll take a guess: the Find method you are using will require that you pass an object (or array of objects) with the same type as the primary key column(s) in your DataTable.

>>Yes. The primary key is ID (access autonumber, type integer) and w.Index is integer


- Have you defined a primary key in your table?
>>Yes. ID.

- If so, presumably the PK has only one column?
>>Yes

- What is the data type of your primary key columns and is it the same as the data type of w.Index?
>>Yes. odbcType.Int for the ID type, and Index is a C# int.

I'm clueless now...

dblost
Saturday, March 05, 2005

If it's happening at the Update, it's probably a problem with the SQL statement.  Check the data types of your SQL statement parameters match the data types of the corresponding SourceColumns in your DataSet.

Joe
Sunday, March 06, 2005

Thank you, Joe. Problems solved -- it was with some mistakes in the update command.

Ah, piece of mind :)

dblost
Sunday, March 06, 2005

*  Recent Topics

*  Fog Creek Home