Data Adapter Update Command

[pendragon]

I am learning ADO.NET by reading Microsofts ADO.NET Core Reference, I understanding how to get data using the data adapter, how to fill a data set and how to use data rows to display and modify the data, but I can not get the data adapter to update the changed data to my database.

 

I have created a small test program to try and figure this out, this is what I do in the Form Load Event.

 

ds = new DataSet();
SQL = "SELECT STCPartID, STCDesc1, STCDesc2 FROM StockParts ORDER BY STCPartID";
cn = new OleDbConnection(dataaccess.STCConString);
cn.Open();

Application.DoEvents();

da = new OleDbDataAdapter(SQL, cn);
da.TableMappings.Add("Table", "StockParts");

tbl = ds.Tables.Add("StockParts");
col = tbl.Columns.Add("STCPartID", typeof(string));
col = tbl.Columns.Add("STCDesc1", typeof(string));
col = tbl.Columns.Add("STCDesc2", typeof(string));
tbl.PrimaryKey = new DataColumn[] {tbl.Columns["STCPartID"]};

dataaccess.tblSTCPart = ds.Tables["StockParts"];
dataaccess.colSTCPartID = dataaccess.tblSTCPart.Columns["STCPartID"];
dataaccess.colSTCDesc1 = dataaccess.tblSTCPart.Columns["STCDesc1"];
dataaccess.colSTCDesc2 = dataaccess.tblSTCPart.Columns["STCDesc2"];

da.Fill(ds, "StockParts");

da.Dispose();
tbl.Dispose();
col.Dispose();
cn.Close();
cn.Dispose();
Application.DoEvents();

StockParts = dataaccess.tblSTCPart.Rows[0];

this.StockID.Text = dataaccess.TextNull((string)StockParts[dataaccess.colSTCPartID]);
this.Description.Text = dataaccess.TextNull((string)StockParts[dataaccess.colSTCDesc1]);
this.Description2.Text = dataaccess.TextNull((string)StockParts[dataaccess.colSTCDesc2]);

 

I but a button on the form to do a change and try to update the database

 

string sql;
		
cn = new OleDbConnection(dataaccess.STCConString);
SQL = "SELECT STCPartID, STCDesc1, STCDesc2 FROM StockParts ORDER BY STCPartID";
da = new OleDbDataAdapter(SQL, cn);
cn.Open();
		
OleDbCommand cmd;

sql = "UPDATE [stockParts]";
sql += " SET STCPartID = ?, STCDesc1 = ?, STCDesc2 = ? ";
sql += " WHERE STCPartID = ? AND STCDesc1 = ? STCDesc2 = ? ";

cmd = new OleDbCommand(sql, cn);

OleDbParameterCollection pc = cmd.Parameters;

pc.Add("STCPartID_New", OleDbType.Char, 0, "STCPartID");
pc.Add("STCDesc1_New", OleDbType.Char, 0, "STCDesc1");
pc.Add("STCDesc2_New", OleDbType.Char, 0, "STCDesc2");

OleDbParameter param;
				
param = pc.Add("STCPartID_Orig", OleDbType.Char, 0, "STCPartID");
param.SourceVersion = DataRowVersion.Original;

param = pc.Add("STCDesc1_Orig", OleDbType.Char, 0, "STCDesc1");
param.SourceVersion = DataRowVersion.Original;

param = pc.Add("STCDesc2_Orig", OleDbType.Char, 0, "STCDesc2");
param.SourceVersion = DataRowVersion.Original;

this.StockParts[dataaccess.colSTCDesc2] = "This Has Been updated By The Data Adapter";

da.UpdateCommand = cmd;
		
da.Update(ds, "StockParts");
ds.AcceptChanges();
MessageBox.Show("The update was successful.");

 

I get the following error on da.update(ds, "StockParts");

 

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

 

but I get no additional information.

 

If I use the command builder to create the update sql it gives me the following

 

UPDATE StockParts SET STCPartID = ? , STCDesc1 = ? , STCDesc2 = ? WHERE ( (STCPartID = ?) AND ((? = 1 AND STCDesc1 IS NULL) OR (STCDesc1 = ?)) AND ((? = 1 AND STCDesc2 IS NULL) OR (STCDesc2 = ?)) )

 

But I still get the error in the same place.

 

Any help on what I am doing wrong will be gladly received, also can anyone suggest a good book to read up on the SQL update, insert and delete commands.

 

Thanks

[JABE]

I haven't really dug deeply into your code though I suspect it has something to do w/ the definition of your parameters. Anyway, you can get a more descriptive error msg by enclosing your code w/in a try/catch block and retrieving the Message of the exception raised.

[pendragon]

JABE

 

I seem to always forget to use the try/catch block to get the exception, it told me exactly where the error was.

 

Just for reference it was on this line

 

sql += " WHERE STCPartID = ? AND STCDesc1 = ? STCDesc2 = ? ";

 

Should off course look like this

 

sql += " WHERE STCPartID = ? AND STCDesc1 = ? AND STCDesc2 = ? ";

 

Thanks for that, now lets see what else I can break :D