Need some help with delete a recordset

Hi, I would like to delete more then one dataset from my table all with the same No. = KalkulationMetallwarenIDNr
How can I do this? My code works fine for the first dataset that was found.
I do not want to use try and error, because I am afraid to delete more than I need.
500 points with a solution.
Thank you for your help.
Best regards,
Thomas

f.e. KalkulationMetallwarenIDNr=3112
there a 5 datasets with the same no, only the first will deleted with my code

 


CString str_KalkulationMetallwarenIDNr;
    m_Edit_KalkulationMetallwarenIDNr.GetWindowText(str_KalkulationMetallwarenIDNr);//KalkulationMetallwarenIDNr
   //-------------------------------------------------------------------------------------
	//SQL Server
	m_strConnection300 = _T("Driver={SQL Server}; Server=DSERVER; Database=OMSDATEN;Uid=;Pwd;");
	//-----------------------------------------------------------------------------------------------------------
	//Initialize the Recordset and binding pointers
	m_ptrRs300 = NULL;   

    m_piAdoRecordBinding300 = NULL;   

	//Initialize the COM environment
	::CoInitialize(NULL);
	try
	{
		//Create the recordset object
		m_ptrRs300.CreateInstance(__uuidof(Recordset));  
        CString sql;
 
  
     //--------------------------------------------------------------------------------------
     sql.Format("SELECT * FROM KalkulationMetallwaren WHERE KalkulationMetallwarenIDNr='%s'",(LPCTSTR)str_KalkulationMetallwarenIDNr );
	 //--------------------------------------------------------------------------------------

        _bstr_t bstrQuery(sql);
  
		//Open the recordset object Tabelle 
         m_ptrRs300->Open(_variant_t(bstrQuery),(LPCTSTR)m_strConnection300, adOpenDynamic, adLockOptimistic, adCmdUnknown);


		//Get a pointer to the record-binding interface Coils
		if(FAILED(m_ptrRs300->QueryInterface(__uuidof(IADORecordBinding),(LPVOID *)&m_piAdoRecordBinding300)))     _com_issue_error(E_NOINTERFACE);
  
		//Bind the record class to the recordset
	 	m_piAdoRecordBinding300->BindToRecordset(&m_rsRecSet300); 

		//------------------------------------------------------------------------------
	  
		//---------------------------------------------
		if(m_ptrRs300->Supports(adDelete))
		{
			//----------------------------
			m_ptrRs300->Delete(adAffectCurrent);
			//----------------------------
			m_ptrRs300->MovePrevious();

		   Kennzahl_KalkulationMetallwaren=1;//Kennzahl Messagebox
		}
	    //---------------------------------------------
			 
	}

//###########################################################################################

		//---------- Any erros? -------------
    	catch (_com_error &e)
		{
			//Display the error
			GenerateError(e.Error(), e.Description());
		}
		//-----------------------------------
       
       
	 //Close the recordset
 	if(m_ptrRs300)  m_ptrRs300->Close();    
	

	// Do we have a valid pointer to the record binding?
	if(m_piAdoRecordBinding300) m_piAdoRecordBinding300->Release();     
	
	//Set the recordset pointer to NULL
	m_ptrRs300 = NULL;     
	
	//Shut down the COM environment
	::CoUninitialize();

Open in new window

Thomas StockbrueggerAsked:
Who is Participating?
 
sarabandeCommented:
yes, looks good.

you also can check the count of the recordset to be not 0.

i didn't use ADO DB myself but native odbc and mfc recordset. the principal concepts are identical. but there are differences in naming. so i didn't know exactly whether it is m_ptrRs200->BOF or m_ptrRs200->BeginOfFile. Or whether the m_ptrRs200->EndOfFile was true also for empty recordsets. in any case it could not be wrong to make sure that you have a valid record before trying to delete it :-)

Sara
0
 
sarabandeCommented:
there a 5 datasets with the same no, only the first will deleted with my code
it is not quite clear how the recordsets 2 to 5 could have more records with the "same no". or do you lookup for a different number  for the other sets? or do you have 5 tables where you look up for the same number 3112?

I do not want to use try and error, because I am afraid to delete more than I need.
why not simply make a backup before trying? and why should the first deletion work and the others not? if you always would use a new connection, it is very unlikely that it works with the first number and fails with others.

if you lookup for different numbers you can consider to using a select statement:

"SELECT * FROM KalkulationMetallwaren WHERE KalkulationMetallwarenIDNr in ('3111",'3112','3115','3120','3122')"

or you use a statement like

"Delete From KalkulationMetallwaren WHERE KalkulationMetallwarenIDNr in ('3111','3112','3115','3120','3122')"

without reading a recordset but executing the statement directly with Execute method of your connection.

Sara
0
 
Thomas StockbrueggerAuthor Commented:
Hello Sara,
thank you for your answer.
Please look at my hardcopy of my sql table.
I had 5 datasets with ABNr 34701 and  Pos 010,  020,  030,  040,  050
With my code only the the first dataset = Pos 010 will deleted.
When the user enters the ABNr to delete the order like 34701 he don´t know how many Pos there are.
So I can not use the Pos in my sql query.
look at my table
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Thomas StockbrueggerAuthor Commented:
Hi Sara,
I found the answer. Before I did not delete more the one dataset at once. So I was not sure to use the while()
But it works fine. Let me know, thanks.
while(!m_ptrRs200->EndOfFile)
	 {
		//---------------------------------------------
		if(m_ptrRs200->Supports(adDelete))
		{
			//---------------------------------------------------
			m_ptrRs200->Delete(adAffectCurrent);
			//---------------------------------------------------
		 
			//Verhindert Error-Meldung BOF / EOF
			if(m_ptrRs200->EndOfFile)break;
			m_ptrRs200->MoveNext();


			Kennzahl_Auftragsbestätigungspositionen=1;//Kennzahl Messagebox
		}
	    //---------------------------------------------

Open in new window

0
 
sarabandeCommented:
i did not recognize that you used the term 'data set' as a synonym for 'data record'. normally a 'set' is more than one record.

yes, you can delete more than one record of a recordset by using a loop that breaks when the EOF was detected.

there is one thing you still may have to consider: if the query returns an empty recordset, the adAffectCurrent is invalid and the EndOfFile also might return false such that you could run into an infinite loop. to prevent from that you only should start the loop if the BOF (BeginOfFile) was true for the first record.

Sara
0
 
Thomas StockbrueggerAuthor Commented:
did you mean that?

 if(m_ptrRs200->BOF) goto Ende_der_while_Schleife;   <------------------------

     while(!m_ptrRs200->EndOfFile)
	 {
		//---------------------------------------------
		if(m_ptrRs200->Supports(adDelete))
		{
			//----------------------------
			m_ptrRs200->Delete(adAffectCurrent);
			//----------------------------
			//m_ptrRs200->MovePrevious();

			//Verhindert Error-Meldung BOF / EOF
			if(m_ptrRs200->EndOfFile)break;
			m_ptrRs200->MoveNext();


			Kennzahl_Auftragsbestätigungspositionen=1;//Kennzahl Messagebox
		}
	    //---------------------------------------------
	
	}
}

		//---------- Any erros? -------------
    	catch (_com_error &e)
		{
			//Display the error
			GenerateError(e.Error(), e.Description());
		}
		//-----------------------------------
        Ende_der_while_Schleife:                  <-----------------------------------------

Open in new window

0
 
Thomas StockbrueggerAuthor Commented:
thanks for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.