johnmullally
asked on
ExecuteNonQuery works, DataAdapter doesn't
I am able to Update and Delete using ExecuteNonQuery, but unable to do so using the DataAdapter - strange. Experienced problems when following a Tutorial that used wizards, dataadapter, datagrid, and SQL Server. But to best diagnose, I transitioned to simple code (first attempt) and added a very simple new table with just 2 fields and no rules or relations in order to rule out dependencies, special database table restriction, or relation problems. The second attempt is the result of debug effort - still short on clues.
In both attempts the DELETE fails with the DataAdapter/Command Builder but the ExecuteNonQuery works.
first attempt:
private void Page_Load(object sender, System.EventArgs e)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "workstation id=JOHN;packet size=4096;" +
"integrated security=SSPI;data source=JOHN;" +
"persist security info=False;initial catalog=Northwind";
conn.Open();
string strSQL1 = "SELECT ShipAddressID, ShipAddress FROM dbo.ShipAddress";
DataSet ds = new DataSet("dsNorthwind");
SqlDataAdapter da = new SqlDataAdapter(strSQL1, conn);
// Delete with DataAdapter does not work
SqlCommandBuilder cb = new SqlCommandBuilder(da);
da.Fill(ds, "ShipAddress");
ds.Tables["ShipAddress"].R ows.Remove At(0);
da.Update(ds, "ShipAddress");
// Delete with SQL Command works
string strSQL3 = "DELETE FROM ShipAddress WHERE ShipAddress='Third Address'";
SqlCommand cmd3 = new SqlCommand(strSQL3, conn);
int count = cmd3.ExecuteNonQuery();
conn.Close();
}
second attempt:
private void Page_Load(object sender, System.EventArgs e)
{
try
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "workstation id=JOHN;packet size=4096;" +
"integrated security=SSPI;data source=JOHN;" +
"persist security info=False;initial catalog=Northwind";
conn.Open();
string strSQL1 = "SELECT ShipAddressID, ShipAddress " +
"FROM dbo.ShipAddress";
DataSet ds = new DataSet("dsNorthwind");
SqlDataAdapter da = new SqlDataAdapter(strSQL1, conn);
da.MissingSchemaAction = MissingSchemaAction.AddWit hKey; // removing doesn't help
// Delete with DataAdapter does not work
SqlCommandBuilder cb = new SqlCommandBuilder(da);
da.UpdateCommand = cb.GetUpdateCommand(); // removing doesn't help
da.DeleteCommand = cb.GetDeleteCommand(); // removing doesn't help
da.Fill(ds, "ShipAddress");
DataRow dr = ds.Tables["ShipAddress"].R ows[0];
string ShipAddress1 = (string)dr["ShipAddress"]; // gets 'First Address'
int numRows1 = ds.Tables["ShipAddress"].R ows.Count; // equals 4
ds.Tables["ShipAddress"].R ows.Remove At(0);
da.Update(ds, "ShipAddress");
dr = ds.Tables["ShipAddress"].R ows[0];
string ShipAddress2 = (string)dr["ShipAddress"]; // now gets 'Second Address'
int numRows2 = ds.Tables["ShipAddress"].R ows.Count; // now equals 3
ds.Tables["ShipAddress"].R eset();
da.Fill(ds, "ShipAddress");
dr = ds.Tables["ShipAddress"].R ows[0];
string ShipAddress3 = (string)dr["ShipAddress"]; // back to 'First Address' since da.Update did nothing
int numRows3 = ds.Tables["ShipAddress"].R ows.Count; // back to 4
Response.Write("Count=" + numRows1.ToString() + "/" + numRows2.ToString()+
"/" + numRows3.ToString() + " Address=" + ShipAddress1 + "/" +
ShipAddress2 + "/" + ShipAddress3);
// above prints: "Count=4/3/4 Address=First Address /Second Address /First Address"
// Delete with SQL Command works
string strSQL3 = "DELETE FROM ShipAddress WHERE ShipAddress='Third Address'";
SqlCommand cmd3 = new SqlCommand(strSQL3, conn);
int count = cmd3.ExecuteNonQuery();
conn.Close();
}
catch (Exception caught)
{
Response.Write(caught.ToSt ring()); // does not execute
}
}
I also tried experimenting with SQL Server to no avail. Spent a lot of time on this already. This problem shows up on 2 different computers.
Thanks for your help.
In both attempts the DELETE fails with the DataAdapter/Command Builder but the ExecuteNonQuery works.
first attempt:
private void Page_Load(object sender, System.EventArgs e)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "workstation id=JOHN;packet size=4096;" +
"integrated security=SSPI;data source=JOHN;" +
"persist security info=False;initial catalog=Northwind";
conn.Open();
string strSQL1 = "SELECT ShipAddressID, ShipAddress FROM dbo.ShipAddress";
DataSet ds = new DataSet("dsNorthwind");
SqlDataAdapter da = new SqlDataAdapter(strSQL1, conn);
// Delete with DataAdapter does not work
SqlCommandBuilder cb = new SqlCommandBuilder(da);
da.Fill(ds, "ShipAddress");
ds.Tables["ShipAddress"].R
da.Update(ds, "ShipAddress");
// Delete with SQL Command works
string strSQL3 = "DELETE FROM ShipAddress WHERE ShipAddress='Third Address'";
SqlCommand cmd3 = new SqlCommand(strSQL3, conn);
int count = cmd3.ExecuteNonQuery();
conn.Close();
}
second attempt:
private void Page_Load(object sender, System.EventArgs e)
{
try
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "workstation id=JOHN;packet size=4096;" +
"integrated security=SSPI;data source=JOHN;" +
"persist security info=False;initial catalog=Northwind";
conn.Open();
string strSQL1 = "SELECT ShipAddressID, ShipAddress " +
"FROM dbo.ShipAddress";
DataSet ds = new DataSet("dsNorthwind");
SqlDataAdapter da = new SqlDataAdapter(strSQL1, conn);
da.MissingSchemaAction = MissingSchemaAction.AddWit
// Delete with DataAdapter does not work
SqlCommandBuilder cb = new SqlCommandBuilder(da);
da.UpdateCommand = cb.GetUpdateCommand(); // removing doesn't help
da.DeleteCommand = cb.GetDeleteCommand(); // removing doesn't help
da.Fill(ds, "ShipAddress");
DataRow dr = ds.Tables["ShipAddress"].R
string ShipAddress1 = (string)dr["ShipAddress"];
int numRows1 = ds.Tables["ShipAddress"].R
ds.Tables["ShipAddress"].R
da.Update(ds, "ShipAddress");
dr = ds.Tables["ShipAddress"].R
string ShipAddress2 = (string)dr["ShipAddress"];
int numRows2 = ds.Tables["ShipAddress"].R
ds.Tables["ShipAddress"].R
da.Fill(ds, "ShipAddress");
dr = ds.Tables["ShipAddress"].R
string ShipAddress3 = (string)dr["ShipAddress"];
int numRows3 = ds.Tables["ShipAddress"].R
Response.Write("Count=" + numRows1.ToString() + "/" + numRows2.ToString()+
"/" + numRows3.ToString() + " Address=" + ShipAddress1 + "/" +
ShipAddress2 + "/" + ShipAddress3);
// above prints: "Count=4/3/4 Address=First Address /Second Address /First Address"
// Delete with SQL Command works
string strSQL3 = "DELETE FROM ShipAddress WHERE ShipAddress='Third Address'";
SqlCommand cmd3 = new SqlCommand(strSQL3, conn);
int count = cmd3.ExecuteNonQuery();
conn.Close();
}
catch (Exception caught)
{
Response.Write(caught.ToSt
}
}
I also tried experimenting with SQL Server to no avail. Spent a lot of time on this already. This problem shows up on 2 different computers.
Thanks for your help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
dabas is correct too
> SqlCommandBuilder cb = new SqlCommandBuilder(da);
Creating a ComandBuilder is not enough!
You still need to use it to create the DeleteCommand
da.DeleteCommand = cb.GetDeleteCommand
Dabas