I'm using ADO.NET/OleDB to connect to an AS400 system. I can sucessfully read the data from my AS400 system into a dataset using a data adapter. However, when I try to make changes to the dataset, nothing happens. Here's is some code...

System.Data.OleDb.OleDbDataAdapter da = new OleDbDataAdapter(
        "SELECT * from HTUSRLIB/NEWFILE(NEWMEMBER)",dbConn);

     DataSet ds = new DataSet("test");

     da.Fill(ds);

     DataRow dr = ds.Tables[0].NewRow();
     //dr = ds.Tables[0].Rows[0];
    
     for(int i = 0; i < ds.Tables[0].Rows[0].ItemArray.Length; i++)
     {
        dr.ItemArray[i] = ds.Tables[0].Rows[0].ItemArray[i];
     }
     dr.ItemArray[0] = "AA";
    
    
     ds.Tables[0].Rows.Add((DataRow)dr);

Everything gets executed normally, but all the fields in dr still remain DBNull. (So nothing in the for loop, nor
dr.ItemArray[0] = "AA"
works)

Any idea why I cannot change anything in my dataset?

Any help will be greatly appreciated.

I haven't worked with updating data thru datasets since most of what I'm dealing with is web based and essentially "one-shot" SQL calls, but here is an observation.

You instantiate the DataAdapter("da"). That constructor you use, according to the MSDN documentation, "Initializes a new instance of the OleDbDataAdapter class with a SelectCommand." However "da" doesn't have an "UpdateCommand" value, so this is most likely the source of the problem. You need to have an UpdateCommand object set up so the data adapter knows how to update the data.

Peter
------------------------------------------------------
Work smarter, not harder.

Peter, thank you for the reply.

However, I think what you said makes sense in light of updating the datasource, but should you not be able to update your in-memory dataset if you used a select command to build it?

MSDN also says that you should build your select command before calling the fill method of the data adapter, and you should build the update/delete/insert command before calling the update method. My problem is not with the update method (well, I haven't gotten there yet), but with changing the dataset.

Thanks,
Ash

Ok, perhaps I misunderstood the original problem.

How are you viewing the data? Perhaps the problem does not lie with the dataset, but rather the method of viewing what's in that dataset.

Peter
------------------------------------------------------
Work smarter, not harder.

Peter, I'm not sure I understand what you mean by viewing the data. However, let me try to elaborate, hoping it answers your question.

I don't have DB2 or any database running on the AS400 system, rather there are a bunch of files. The one I'm reading from has columns and data in each column. This is the first time I'm working with AS400, so I don't know the details, but when using the IBM client access program to download an AS400 file to a text file on PC, I see several columns and data beneath each column.
The data adapter has no problem reading from a file; it reads the column headers and column data just fine.

After I fill my dataset using the data adapter, I can see some fields as being strings and others as decimals. I can see the data in my watch list while I step through the program. When I try to change any field in the dataset, it complains (only in the watch window) that it cannot make that assignment. (I made sure I was trying to assign a string to a string, and a decimal to a decimal).

Would appreciate any help....Thanks

"Watch list" is what I was looking for. I was wondering how you were actually looking at the contents of the dataset. I thought you might be looking at it with a label in a form, or maybe a datagrid. The case of a datagrid, I would have said that you might need to re-bind the datasource after you make changes, but seeing as you are looking into the dataset with the debugger watch window, you should see the updates. Isn't there some read/write property of a DataSet/DataAdapter? Again, like I mentioned, I don't use datasets for read/write operations, only read so I haven't worked with it from the update side. This is probably beyond my scope of knowledge.

Peter
------------------------------------------------------
Work smarter, not harder.

Thanks for your time, nonetheless.

Hi,
I think you should call the method acceptchanges of the dataset

Thanks

The reason your updates don't take is because you have only changed the dataset (in memory) not the actual AS400 table.

Application of updates occurs thru the adapter not the dataset.


In your example you need to issue the update method of the adapter.

da.update(ds)

its not sufficent alone u should be sure u sends UpdateCommand in ur adapter with a valid UpdateCommand or u can use simpily CommandBuilder objects(like SqlCommandBuilder)it sends the essential queries automatically.
Have a Look at this :
http://p2p.wrox.com/topic.asp?TOPIC_...commandbuilder

--------------------------------------------
Mehdi.:)