DataAdapter UpdateCommand not updating record

[susan.f.barrett]

Hi,

Despite me being able to type the following in to SQL Server and it
updating 1 row:

updatestockcategory 1093, 839

In my code, it is not updating any rows.

dataSet = new DataSet();
dataAdapter = new SqlDataAdapter(SQLCommandString, conn);
dataAdapter.Fill(dataSet, "StockCategory");

dataTable = dataSet.Tables["StockCategory"];

dataAdapter.UpdateCommand = new
SqlCommand("UpdateStockCategory", conn);
dataAdapter.UpdateCommand.CommandType =
CommandType.StoredProcedure;

myParam =
dataAdapter.UpdateCommand.Parameters.Add("@ParentStockCategoryId",
SqlDbType.Int, 0, "ParentStockCategoryId");
myParam.Value = ParentStockCategoryId;

myParam =
dataAdapter.UpdateCommand.Parameters.Add("@StockCategoryId",
SqlDbType.Int, 0, "StockCategoryId");
myParam.Value = StockCategoryId;

MessageBox.Show(dataAdapter.Update(dataSet,
"StockCategory").ToString());

The last line displays how many records were affected, which in this
case is 0. I am absoluetely sure that ParentStockCategoryId and
StockCategoryId have the right values so I don't know what I could be
doing wrong, it's been bothering me for hours!

Thanks

 

[Guest]

Susan,
I think this was already covered in a similar post. The DataTable in your
dataset that you want to pass to your dataAdapter for the Update method has
one or more rows. Each of those rows has a RowState property. You may wish
to check this property on each row your source Datatable to see what I'm
referring to.
In order for the DataAdapter to actually call the update SQL or Stored Proc
on a particular row, its RowState must be "Modified".
Usually this happens because you have edited data in a datagrid for example,
where this datatable is the underlying DataSource. So if you edit a cell in
the Datagrid, the unnderlying row's RowState will be changed to Modified.
However in this case where you are simply pulling data out of the Database,
I don't think that will happen.
Hope that helps.
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com

Hi,

Despite me being able to type the following in to SQL Server and it
updating 1 row:

updatestockcategory 1093, 839

In my code, it is not updating any rows.

dataSet = new DataSet();
dataAdapter = new SqlDataAdapter(SQLCommandString, conn);
dataAdapter.Fill(dataSet, "StockCategory");

dataTable = dataSet.Tables["StockCategory"];

dataAdapter.UpdateCommand = new
SqlCommand("UpdateStockCategory", conn);
dataAdapter.UpdateCommand.CommandType =
CommandType.StoredProcedure;

myParam =
dataAdapter.UpdateCommand.Parameters.Add("@ParentStockCategoryId",
SqlDbType.Int, 0, "ParentStockCategoryId");
myParam.Value = ParentStockCategoryId;

myParam =
dataAdapter.UpdateCommand.Parameters.Add("@StockCategoryId",
SqlDbType.Int, 0, "StockCategoryId");
myParam.Value = StockCategoryId;

MessageBox.Show(dataAdapter.Update(dataSet,
"StockCategory").ToString());

The last line displays how many records were affected, which in this
case is 0. I am absoluetely sure that ParentStockCategoryId and
StockCategoryId have the right values so I don't know what I could be
doing wrong, it's been bothering me for hours!

Thanks

 

[Guest]

P.S. -- I think what you really are wanting to do is something more like this:

dataSet = new DataSet();
dataAdapter = new SqlDataAdapter(SQLCommandString, conn);
dataAdapter.Fill(dataSet, "StockCategory");
dataTable = dataSet.Tables["StockCategory"];

SqlCommand UpdateCommand = new SqlCommand("UpdateStockCategory", conn);
UpdateCommand.CommandType =CommandType.StoredProcedure;
foreach(DataRow row in dataTable)
{
myParam=UpdateCommand.Parameters.Add("@ParentStockCategoryId",
SqlDbType.Int, 0, "ParentStockCategoryId");
myParam.Value = row["ParentStockCategoryId"];
myParam
=dataAdapter.UpdateCommand.Parameters.Add("@StockCategoryId",SqlDbType.Int,
0, "StockCategoryId");
myParam.Value = row["StockCategoryId"];
int result=UpdateCommand.ExecuteNonQuery();
}
conn.Close();

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com

Hi,

Despite me being able to type the following in to SQL Server and it
updating 1 row:

updatestockcategory 1093, 839

In my code, it is not updating any rows.

dataSet = new DataSet();
dataAdapter = new SqlDataAdapter(SQLCommandString, conn);
dataAdapter.Fill(dataSet, "StockCategory");

dataTable = dataSet.Tables["StockCategory"];

dataAdapter.UpdateCommand = new
SqlCommand("UpdateStockCategory", conn);
dataAdapter.UpdateCommand.CommandType =
CommandType.StoredProcedure;

myParam =
dataAdapter.UpdateCommand.Parameters.Add("@ParentStockCategoryId",
SqlDbType.Int, 0, "ParentStockCategoryId");
myParam.Value = ParentStockCategoryId;

myParam =
dataAdapter.UpdateCommand.Parameters.Add("@StockCategoryId",
SqlDbType.Int, 0, "StockCategoryId");
myParam.Value = StockCategoryId;

MessageBox.Show(dataAdapter.Update(dataSet,
"StockCategory").ToString());

The last line displays how many records were affected, which in this
case is 0. I am absoluetely sure that ParentStockCategoryId and
StockCategoryId have the right values so I don't know what I could be
doing wrong, it's been bothering me for hours!

Thanks