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

This is a discussion on (C# and ADO.NET) ExecuteNonQuery() problem within the C# Programming forums, part of the General Programming Boards category; Hi there! how I can find out why ExecuteNonQuery() return 0 and not 1 by submit? at this moment I ...

  1. #1
    Registered User
    Join Date
    Oct 2002
    Posts
    101

    Question (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

  2. #2
    the hat of redundancy hat nvoigt's Avatar
    Join Date
    Aug 2001
    Location
    Hannover, Germany
    Posts
    3,139
    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 ?
    hth
    -nv

    She was so Blonde, she spent 20 minutes looking at the orange juice can because it said "Concentrate."

    When in doubt, read the FAQ.
    Then ask a smart question.

  3. #3
    Registered User
    Join Date
    Oct 2002
    Posts
    101
    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

  4. #4
    Registered User
    Join Date
    Oct 2002
    Posts
    101
    I think the problem is the SaleContractID.

    because without SaleContractID it works fine



    gicio

  5. #5
    Registered User
    Join Date
    Oct 2002
    Posts
    101
    SaleContractID is null when the car isn't added to any sale contract.



    gicio

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. ASP and ADO.net question
    By yavanna in forum C# Programming
    Replies: 2
    Last Post: 10-10-2005, 04:44 PM
  2. ADO.Net step by step
    By black in forum C# Programming
    Replies: 1
    Last Post: 04-02-2004, 01:54 AM
  3. VS C++ db with ado.net
    By student2005 in forum C++ Programming
    Replies: 2
    Last Post: 11-27-2003, 02:46 PM
  4. ADO.NET and Relational Data problem.....how start?
    By gicio in forum C# Programming
    Replies: 2
    Last Post: 03-15-2003, 09:25 AM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21