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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Thomas StockbrueggerAuthor Commented:
thanks for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual C++.NET

From novice to tech pro — start learning today.