Need some help with ADODB.Recordset

 //-------------------------------------------------------------------------------------------------------------------
   CString m_strConnection = _T("Driver={SQL Server}; Server=DSERVER; Database=OMSDATEN;Uid=;Pwd;");
   CString str_Table=”Customer”;
   LPCSTR Table1 = (LPCSTR)(LPCSTR)str_Table;
   int Sales=0;
   //-------------------------------------------------------------------------------------------------------------------

   _RecordsetPtr rsTipi_1 ("ADODB.Recordset");
    rsTipi_1->Open(Table1, (LPCTSTR) m_strConnection, adOpenForwardOnly,adLockOptimistic, adCmdTable);

   FieldsPtr fldTipi_1 = rsTipi_1->Fields;
   rsTipi_1->Update();

   fldTipi_1->Item["No"]->Value                        =  (LPCSTR) str_No;
   fldTipi_1->Item["Name"]->Value                   =  (LPCSTR) str_Name;
   fldTipi_1->Item["Sales"]->Value                     =  Sales;


   rsTipi_1->Update();
   rsTipi_1->Close();

Open in new window




This works just fine. I can open my sql table Customer and add some new data, but only one row.


But I would like to use an sql query like this:

("SELECT * FROM '%s'  WHERE Order_No='%s'",Table1,(LPCTSTR)str_Order_No)
so I would like to find all rows of my Table “Customer” with Order_No = str_Order_No and change all of these rows with new
data.

I do not know where and how to put the query and change the code?

Please let me know, thank you very much.
Best regards,
Thomas
Thomas StockbrueggerAsked:
Who is Participating?
 
sarabandeConnect With a Mentor Commented:
no, a smart pointer for a recordset only should be a local variable. so you create it, make open in order to perform the query what makes that the recordset either is empty (begin of file - BOF - is false) or points to the first record of the record set, then evaluate each record and move to next record until EOF. finally close the recordset and all is done. you never should use a record set twice. if you really have to repeat the query create a new recordset pointer and do again. the problem is, for a recordset already used you would not easily have a full reset. if you change anything, e. g. add or delete or modify records, the recordset would NOT necessarily be changed properly. also databases could be changed from other programs and you might get concurrency or locking issues, if you hold a recordset longer as needed.

so, you simply change the reurn type of your function and return a bool (success or failure) as return value.

I have put the pointer in the h.file 

Open in new window


i hope you meant that you put it as a class member ?

if you made a global variable you never may put it into a header file, because the header file cannot be included by any second cpp file as the variable would be defined twice in your project (every include statement creates the global variable again). then the compiler will not complain but the linker, because duplicate global variables are not valid and give linker errors.

you can define global variables at top of your cpp file below include statements although the usage of global variables in c++ is seen as bad practice. so, first choice always is a local variable within a scope as small as possible. second choice is arguments passed to the function by value. wih this your function has input values only and cannot spoil any variables of the caller. third choice is class member variables in member functions. then static variables either locally defined in the function or static class members. the latter is somewhat better as global variables.

Sara
0
 
Nitin SontakkeDeveloperCommented:
Decided to put some comments as nobody seem to pick this thread. I have no knowledge of C++. Only the SQL server and about few hundred IT years back have worked with ADODB.Recordset. So essentially have forgotten much of it.

There are two approaches, one is table based and another is sql statement based. I have always worked with the sql statement based method and the recordset method you have shown above.

Instead of the select and the modify data in memody and update db (as shown in your code) if you know what and where you want to modify, i suggest you just prepare and appropriate update statement and execute it.

For example: update customer set column = values, column = value where order_no = 5 and execute it. You can execute it as a recordset returning, but it will not return any rows just update the the data in database.
1
 
Thomas StockbrueggerAuthor Commented:
_RecordsetPtr rsTipi_1 ("ADODB.Recordset");
   try
   {

   LPCSTR TABLE = (LPCSTR)(LPCSTR)("SELECT * FROM '%s'",str_Name_of_my_table);
   rsTipi_1->Open(TABLE, (LPCTSTR) m_strConnection, adOpenDynamic, adLockOptimistic, adCmdUnknown);


  
 
   FieldsPtr fldTipi_1 = rsTipi_1->Fields;
   //--------------------------------------------
   if(rsTipi_1->BOF) goto Ende_der_while_Schleife1;
   //--------------------------------------------
   rsTipi_1->MoveFirst();

      while(!rsTipi_1->EndOfFile)
      {
              rsTipi_1->Update();

             fldTipi_1->Item["No"]->Value                        =  (LPCSTR) str_No;
             fldTipi_1->Item["Name"]->Value                   =  (LPCSTR) str_Name;
             fldTipi_1->Item["Sales"]->Value                     =  Sales;

          rsTipi_1->Update();
          rsTipi_1->MoveNext();
        }

   }
        	
		//---------- Any erros? -------------
    	       catch (_com_error &e)
		{
			//Display the error
			GenerateError(e.Error(), e.Description());
		}
		//-----------------------------------
	   //-----------------------------------
         Ende_der_while_Schleife1:
	   //-----------------------------------
	 
   rsTipi_1->Close();
   //======================================================================================

Open in new window


I found a solution myself.
I do not need this ("SELECT * FROM '%s'  WHERE Order_No='%s'",Table1,(LPCTSTR)str_Order_No)
I changed it to: ("SELECT * FROM '%s'",str_Name_of_my_table); because all of the data has the same Order_No and also did some changes with move first and move next.
It works just fine.
Thank you.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Nitin SontakkeDeveloperCommented:
Good to hear that. You may please formally close the ticket without giving any points.

For record, preparing a single update statement and issueing it to database is the best approach from performance point of view in general and from relation database theory in particular.

The code posted above is pre-relational (dBase III+, FoxPro, etc.) where operation MUST BE performed row-by-row.

With relational approach, it is highly suggested that you perform operations on a set of data (rows).

The sole purpose of this comment is to make you aware (if you are not already), just so that it should not happen that nobody ever told you this.

Please take in right spirit!
0
 
sarabandeCommented:
LPCSTR TABLE = (LPCSTR)(LPCSTR)("SELECT * FROM '%s'",str_Name_of_my_table);

you may look with the debugger for the value stored in TABLE after the above statement.

it is NOT

"SELECT * FROM Customer"

Open in new window



as you might assume but only

"Customer"

Open in new window


that is because the rvalue expression right from the assign operator =   actually is two expressions separated with comma

 LPCSTR)(LPCSTR)("SELECT * FROM '%s'"

Open in new window


and CString variable

str_Name_of_my_table

Open in new window


the first expression was not used as the second expression overwrites it. the reason is simple: there is no function (call) which would replace the %s by the CString value. such function would have been the CString::Format member function.

so, the following code would have worked:

    CString TABLE;
    TABLE.Format("SELECT * FROM '%s'", str_Name_of_my_table);

Open in new window


even so, you were lucky that CString has a so-called cast operator which turns a CString object to a const char * (alternatively named LPCSTR) if needed and that the Format function is able to take a CString as variable argument type instead of a char pointer. for the same reason, you wouldn't need to cast the str_Name_of_my_table to LPCSTR (and definitively not two times) but simply could assign it.

LPCSTR TABLE = str_Name_of_my_table;

Open in new window



It works just fine.

but why did it work by giving only the table name? that is a feature of the recordset open function which exactly turns the table name into the select * from table as required.

so if you would need to go your first approach and have some different order numbers, do like

CString strSelect;
strSelect.Format("Select No,Name,Sales from Customer where NO='%s' ", strOrderNumber); 

rsTipi_1->Open(strSelect, m_strConnection, adOpenDynamic, adLockOptimistic, adCmdUnknown);

Open in new window


as told, you don't need to cast CString variables if a const char * is required.

last thing: don't use goto in your code.

simply do:

   if(rsTipi_1->BOF) 
    {
           rsTipi_1->Close();
           return NULL;    // why do you return a pointer from this function at all ???
   }
   //--------------------------------------------
   rsTipi_1->MoveFirst();

      while(!rsTipi_1->EndOfFile)
      {
            .... 

Open in new window


Sara
0
 
Thomas StockbrueggerAuthor Commented:
Hello Sara,
thank you for your answers.
You are right,
LPCSTR TABLE = (LPCSTR)(LPCSTR)("SELECT * FROM '%s'",str_Name_of_my_table);

Open in new window

TABLE = has only the "Customer"

CString TABLE;
 TABLE.Format("SELECT * FROM '%s'", str_Name_of_my_table);
rsTipi_1->Open(strSelect, m_strConnection, adOpenDynamic, adLockOptimistic, adCmdUnknown);

Open in new window


I will get the following error:
ADOCG::Recordset15::Open': Konvertierung des Parameters 1 von 'CString' in 'const _variant_t &' nicht möglich
        Ursache: Konvertierung von 'CString' in 'const _variant_t' nicht möglich
0
 
Thomas StockbrueggerAuthor Commented:
CString TABLE;
 TABLE.Format("SELECT * FROM '%s'", str_Name_of_my_table);
rsTipi_1->Open(TABLE, m_strConnection, adOpenDynamic, adLockOptimistic, adCmdUnknown);

Open in new window

sorry I mean this:
0
 
Thomas StockbrueggerAuthor Commented:
Sara,
I found my mistake:   SELECT * FROM %s ..........on a  table name I can not use the ' '
I changed my code  to to this, and it works just fine.
It works before, because every row of my table had the same OrderNo (SELECT *...)
If I had  more than one OderNo before,  I would have seen my mistake.


CString TABLE;
 TABLE.Format("SELECT * FROM %s WHERE No='%s'",str_Name_of_my_table,strOrderNumber);

_bstr_t bstrQuery(TABLE);

rsTipi_1->Open(_variant_t(bstrQuery),(LPCTSTR)m_strConnection100, adOpenDynamic, adLockOptimistic, 

Open in new window

0
 
Thomas StockbrueggerAuthor Commented:
m_strConnection100 sorry I was to fast...

rsTipi_1->Open(_variant_t(bstrQuery),(LPCTSTR)m_strConnection, adOpenDynamic, adLockOptimistic, adCmdUnknown);

Open in new window

0
 
Thomas StockbrueggerAuthor Commented:
last thing: don't use goto in your code.

if(rsTipi_1->BOF)
    {
           rsTipi_1->Close();
           return NULL;    // why do you return a pointer from this function at all ???
   }
because
.
.
.
.
        //-----------------------------------
         Ende_der_while_Schleife1:
         //-----------------------------------
      
   rsTipi_1->Close();
   //======================================================================================

here is some add code
..........
..........
...........
0
 
sarabandeCommented:
if(rsTipi_1->BOF)

that means the table is empty or the query has no match. if you have additional code which can handle both a valid result set AND an empty result set, i would assume that your function is not well defined. you should put those additional statements into a separate function and call first the query function and then the additional function.

but in my opinion you can't do some valid thing in case of an empty result set.

why do you return a pointer from this function at all ???
a _RecordsetPtr is a smart pointer pointing to a Recordset object. smart pointer means you don't need to care for deletion, it will be deleted if no part of your program holds a copy of the pointer and the original instance goes out-of-scope (means out of the {} block where the smart pointer was created). nevertheless it has pointer semantics and it is seen as bad practice if you create a pointer in a function and return it to the calling function, as creation and deletion of a pointer should happen at the same calling level.

you shouldn't return a pointer from a function if the pointer was created in the function. if you need the query in the calling function, you should create the pointer there and pass it as an argument. for your current code i can't see any value into returning a recordset query where you already have read (and evaluated) all records in a loop. you definitively should avoid to using the same recordset again.

Sara
0
 
sarabandeCommented:
if you think your design is right you also can do (for to avoid goto)

    if(!rsTipi_1->BOF)
    {
           // put here the while loop and evaluation of the recordset
           ...
    }
    rsTipi_1->Close();
    // additional code
    .... 
    // try to avoid returning a pointer
    // probably the calling function doesn't really need the recordset
    // or if it does, create a new recordset
    return rsTipi_1;   

Open in new window


Sara
0
 
Thomas StockbrueggerAuthor Commented:
  in h.file
 _RecordsetPtr rsTipi


    rsTipi =NULL;
    rsTipi.CreateInstance(__uuidof(Recordset));  
   rsTipi->Open(Table, (LPCTSTR) m_strConnection, adOpenForwardOnly,adLockOptimistic, adCmdTable);

    
   FieldsPtr fldTipi = rsTipi->Fields;
   rsTipi->Update();

   fldTipi->Item["ABNr"]->Value                          =  (LPCSTR) str_LfdNrAuftragsbestaetigungen; //neue AuftragsNr
    .
    .
    .
    .
    .
   
   rsTipi->Update();
   rsTipi->Close();

Open in new window

Sara, what do you think about this. I have put the pointer in the h.file
0
 
Thomas StockbrueggerAuthor Commented:
Thank you
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.