Hi there!
how I can find out why ExecuteNonQuery() return 0 and not 1 by submit?
at this moment I see no chance to know why my query isn't submited.
thx
gicio
Printable View
Hi there!
how I can find out why ExecuteNonQuery() return 0 and not 1 by submit?
at this moment I see no chance to know why my query isn't submited.
thx
gicio
Can you post your statement ? Have you tried running your statement on the datasource and watching the results ?Quote:
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.
Hi,
yes I running this statement on the datasource and it works.
Code:internal void ChangeCar(ArrayList p_arlCars, CarTrade p_CarTrade)
{
m_objCarTrade = p_CarTrade;
for(int i = 0; i < p_arlCars.Count ;i++)
{
foreach(DataRow row in p_CarTrade.DataSetCarTrade.Tables["Car"].Rows)
{
//return true if CarID is the same
m_blnCarIDisTheSame = (row["CarID"].ToString() == ((Car)p_arlCars[i]).Id);
if(m_blnCarIDisTheSame)
{
row["CarID"] = ((Car)p_arlCars[i]).Id;
row["Manufacture"] = ((Car)p_arlCars[i]).Manufacturer;
row["Color"] = ((Car)p_arlCars[i]).Color;
row["Hp"] = ((Car)p_arlCars[i]).Hp;
row["Displacement"] = ((Car)p_arlCars[i]).Displacement;
row["Price"] = ((Car)p_arlCars[i]).Price;
row["Model"] = ((Car)p_arlCars[i]).Model;
row["SaleContractID"] = ((Car)p_arlCars[i]).SaleContactID;
SubmitChanges();
break;
}
}
}
}
Code:private OleDbCommand CreateUpdateCommand()
{
m_strSQL = "UPDATE Car SET CarID = ?, Manufacture = ?, Color = ?, Hp = ?, Displacement " +
"= ?, Price = ?, Model = ?, SaleContractID = ? WHERE CarID = ? AND " +
"Manufacture = ? AND Color = ? AND Hp = ? AND Displacement = ? AND Price = " +
"? AND Model = ? AND SaleContractID = ?";
OleDbCommand cmd = new OleDbCommand(m_strSQL, m_objCarTrade.OleDbConnectionCarTrade);
OleDbParameterCollection pc = cmd.Parameters;
pc.Add("CarID_New", OleDbType.LongVarChar);
pc.Add("Manufacture_New", OleDbType.LongVarChar);
pc.Add("Color_New", OleDbType.LongVarChar);
pc.Add("Hp_New", OleDbType.Single);
pc.Add("Displacement_New", OleDbType.Single);
pc.Add("Price_New", OleDbType.Integer);
pc.Add("Model_New", OleDbType.LongVarChar);
pc.Add("SaleContractID_New", OleDbType.LongVarChar);
pc.Add("CarID_Orig", OleDbType.LongVarChar);
pc.Add("Manufacture_Orig", OleDbType.LongVarChar);
pc.Add("Color_Orig", OleDbType.LongVarChar);
pc.Add("Hp_Orig", OleDbType.Single);
pc.Add("Displacement_Orig", OleDbType.Single);
pc.Add("Price_Orig", OleDbType.Integer);
pc.Add("Model_Orig", OleDbType.LongVarChar);
pc.Add("SaleContractID_Orig", OleDbType.LongVarChar);
return cmd;
}
Code:private void SubmitChanges()
{
DataViewRowState dvrs;
dvrs = DataViewRowState.ModifiedCurrent|DataViewRowState.Deleted|DataViewRowState.Added;
int intRowsAffected = 0;
DataRow[] dtrModified = m_objCarTrade.DataSetCarTrade.Tables["Car"].Select("", "", dvrs);
try
{
//we open a connection to DB
m_objCarTrade.OleDbConnectionCarTrade.Open();
foreach (DataRow row in dtrModified)
{
switch (row.RowState)
{
case DataRowState.Modified:
OleDbCommand cmdUpdate = CreateUpdateCommand();
intRowsAffected = SubmitUpdate(row, cmdUpdate);
break;
case DataRowState.Added:
OleDbCommand cmdInsert = CreateInsertCommand();
intRowsAffected = SubmitInsert(row, cmdInsert);
break;
case DataRowState.Deleted:
OleDbCommand cmdDelete = CreateDeleteCommand();
intRowsAffected = SubmitDelete(row, cmdDelete);
break;
}
if(intRowsAffected == 1)
{
row.AcceptChanges();
MessageBox.Show("Data base update successful");
}
else
{
row.RowError = "Update attempt failed";
MessageBox.Show(row.RowError);
}
}
}
catch(OleDbException e)
{
MessageBox.Show(e.Message);
}
finally
{
//no matter what happens we close the connection to DB
m_objCarTrade.OleDbConnectionCarTrade.Close();
}
}
Code:private int SubmitUpdate(DataRow p_row, OleDbCommand p_cmd)
{
OleDbParameterCollection pc = p_cmd.Parameters;
pc["CarID_New"].Value = p_row["CarID"];
pc["Manufacture_New"].Value = p_row["Manufacture"];
pc["Color_New"].Value = p_row["Color"];
pc["Hp_New"].Value = p_row["Hp"];
pc["Displacement_New"].Value = p_row["Displacement"];
pc["Price_New"].Value = p_row["Price"];
pc["Model_New"].Value = p_row["Model"];
pc["SaleContractID_New"].Value = p_row["SaleContractID"];
pc["CarID_Orig"].Value = p_row["CarID", DataRowVersion.Original];
pc["Manufacture_Orig"].Value = p_row["Manufacture", DataRowVersion.Original];
pc["Color_Orig"].Value = p_row["Color", DataRowVersion.Original];
pc["Hp_Orig"].Value = p_row["Hp", DataRowVersion.Original];
pc["Displacement_Orig"].Value = p_row["Displacement", DataRowVersion.Original];
pc["Price_Orig"].Value = p_row["Price", DataRowVersion.Original];
pc["Model_Orig"].Value = p_row["Model", DataRowVersion.Original];
pc["SaleContractID_Orig"].Value = p_row["SaleContractID" , DataRowVersion.Original];
return p_cmd.ExecuteNonQuery();
}
gicio
I think the problem is the SaleContractID.
because without SaleContractID it works fine ;)
gicio
SaleContractID is null when the car isn't added to any sale contract.
gicio