Thread: (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

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