(C# and ADO.NET) ExecuteNonQuery() problem

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

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

Can you post your statement ? Have you tried running your statement on the datasource and watching the results ?

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