Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2529
  • Last Modified:

calling stored procedures ms sql server in visual c++

I am using MFC, odbc, MS sql server, visual c++ 6.0.  I created a stored procedure gets the information as 20060701.  I need to pass that information into the variable ( BillMonth).  How can I call the stored procedure in visual c++.  I am new to visual c++ and I need coding for that.  For this do I need to create the class? Or what do I have to do?   Following is the code I did it but it’s not working.  

char *BillMonth = (char*)((LPCTSTR)BillMonth);
      /*CString   BillMonth; */

      try
      {
            BillMonth->Open(CRecordset::snapshot, "{call AspGetTransDate}",
                  CRecordset::readOnly | CRecordset::useMultiRowFetch);
      }
      catch(CDBException *e)
      {
            PrintStr(e->m_strError, OutPutFile, true);
            ExitVertex(dConnHdl);
            ExitApp(&cb, DataStrBad);
            return;
      }

Thanks,
Rosh
0
Roshini
Asked:
Roshini
  • 11
  • 10
  • 7
2 Solutions
 
RoshiniAuthor Commented:
I need the coding.  Do you see any modifications for my code.  Do I need to create the class etc.  I am confused.
0
 
mahesh1402Commented:
look at articles they have required coding for your purpose....you need to create CRecordSet derived class

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

 
mahesh1402Commented:
and also you can grab ready created class from above and directly use for your purpose

e.g look at StoredProcedure class from : http://www.codeguru.com/Cpp/data/mfc_database/storedprocedures/article.php/c1167/ 

-MAHESH
0
 
mahesh1402Commented:
also here  
BillMonth->Open(CRecordset::snapshot, "{call AspGetTransDate}",.. <== remove quotes "

try :

BillMonth->Open(CRecordset::snapshot, {call AspGetTransDate},

-MAHESH
0
 
mahesh1402Commented:
sorry it shld be type

"{call procedure_name(params....)}"
0
 
RoshiniAuthor Commented:

I changed it to the following

char *BillMonth = (char*)((LPCTSTR)BillMonth);
      /*CString   BillMonth; */

      try
      {
            BillMonth->Open(CRecordset::snapshot, {call AspGetTransDate},
                  CRecordset::readOnly | CRecordset::useMultiRowFetch);
      }



      catch(CDBException *e)
      {
            PrintStr(e->m_strError, OutPutFile, true);
            ExitVertex(dConnHdl);
            ExitApp(&cb, DataStrBad);
            return;
      }

I got following error messages


C:\CalcTax\CalcTax.cpp(339) : error C2227: left of '->Open' must point to class/struct/union
C:\CalcTax\CalcTax.cpp(339) : error C2143: syntax error : missing ')' before '{'
C:\CalcTax\CalcTax.cpp(339) : error C2059: syntax error : ')'
C:\CalcTax\CalcTax.cpp(339) : error C2143: syntax error : missing ';' before '{'
C:\CalcTax\CalcTax.cpp(339) : error C2065: 'call' : undeclared identifier
C:\CalcTax\CalcTax.cpp(339) : error C2146: syntax error : missing ';' before identifier 'AspGetTransDate'
C:\CalcTax\CalcTax.cpp(339) : error C2065: 'AspGetTransDate' : undeclared identifier
C:\CalcTax\CalcTax.cpp(339) : error C2143: syntax error : missing ';' before '}'
C:\CalcTax\CalcTax.cpp(339) : error C2143: syntax error : missing ';' before ','
C:\CalcTax\CalcTax.cpp(340) : error C2059: syntax error : ')'
Error executing cl.exe.

CalcTax.obj - 10 error(s), 0 warning(s)

0
 
mahesh1402Commented:
Roshini,
  BillMonth is supposed to CRecordSet derived class and not char *..i suggest you to look at above link how to create CRecordSet derived class and how to call stored procedure

-MAHESH
0
 
bastibartelCommented:
CRecordset BillMonth;

 try
 {
          //** If you do overwrite the function CRecordset::GetDefaultConnect()
          //** This will work, but eventually throw an exception as soon as the framework tries to exchange the data with the CRecordset object
          //** you really need to derive from CRecordset and create a class tailored to store the information retrieved from the db query / table, ....

          BillMonth->Open(CRecordset::snapshot, "Name of Stored Proc");
 }
 catch (CDBException *e)
 {;}

//  CRecordset::readOnly is usually not supported by you ODBC driver
//  CRecordset::useMultiRowFetch is not reccommendable because you'll have restrictions in navigating the recordset
 

It is generally recommendable to use the class wizard to create you own recordset class.
The class wizard will allow you to directly select the stored procedur/table and create the corresponding c++ class
The class wizard will also create the member function GetDefaultConnect() GetDefaultSQL() for you.
So without further coding, your recordset set class WILL connect and retreive the data you created it for.

Cheers,
Sebastian

0
 
mahesh1402Commented:
correct syntax is :

  //BillMonth is CRecordSet / derived class

  BillMonth->Open(CRecordset::snapshot, "{CALL Name_of_Stored_Proc}");

//and if you wan to pass parameters

  BillMonth->Open(CRecordset::snapshot, "{CALL Name_of_Stored_Proc(parameter1,parameter2)}");


so you may try like :
BillMonth->Open(CRecordset::snapshot, "{CALL AspGetTransDate}", CRecordset::readOnly | CRecordset::useMultiRowFetch);

-MAHESH
0
 
bastibartelCommented:
Sorry, CRecordset::readOnly works of course - what I meant was 'exclusive access' - My mistake
Sebastian
0
 
RoshiniAuthor Commented:
I created a CRecordset class as

#ifndef _BILLMONTH_H
#define      _BILLMONTH_H
#endif

#include "CalcTax.h"

class CBillMonth : public CRecordset
{
public:
      CBillMonth(CDatabase* pDatabase = NULL);


      CString       BillMonth;
      

};

And calling the this in my main function as

CBillMonth*   BillMonth = new CBillMonth(&cb);
      
      
      try
      {
            BillMonth->Open(CRecordset::snapshot, "{call AspGetTransDate}",
                  CRecordset::readOnly | CRecordset::useMultiRowFetch);
      }



      catch(CDBException *e)
      {
            PrintStr(e->m_strError, OutPutFile, true);
            ExitVertex(dConnHdl);
            ExitApp(&cb, DataStrBad);
            return;
      }

I needed to pass the BillMonth in one of my API call getting the following error message.
Compiling...
CalcTax.cpp
C:\CalcTax\CalcTax.cpp(793) : error C2819: type 'CBillMonth' does not have an overloaded member 'operator ->'
        c:\calctax\bulkrowset.h(69) : see declaration of 'CBillMonth'
C:\CalcTax\CalcTax.cpp(793) : error C2227: left of '->BillMonth' must point to class/struct/union
Error executing cl.exe.

CalcTax.obj - 2 error(s), 0 warning(s)

Don't know why.  Thanks for your help guys and needed the help asap.


0
 
bastibartelCommented:
Please post the code fragments, generating these errors.

Cheers,
Sebastian
0
 
RoshiniAuthor Commented:
I passed BillMonth in this API call and gets the error

if (VstSetLineDetail(taxHdl,
                                                      pLInfo->lineItemNumber,
                                                      pLInfo->transactionType,
                                                      eVstTransSubTypeNone,
                                                      eVstTransCdNormal,
                                                      /*NULL,      */
                                                      /*"20060701" */
                                                      CBillMonth->BillMonth,
                                                      &lTransTaxedGeoFlag,
                                                      &pLInfo->extensionAmount,
                                                      NULL,NULL,NULL,
                                                      NULL,NULL,NULL,locationCode,NULL) == FALSE)
If a put BillMonth in double coats it will be okay.  otherwise I gets the error as

Compiling...
CalcTax.cpp
C:\CalcTax\CalcTax.cpp(793) : error C2819: type 'CBillMonth' does not have an overloaded member 'operator ->'
        c:\calctax\bulkrowset.h(69) : see declaration of 'CBillMonth'
C:\CalcTax\CalcTax.cpp(793) : error C2227: left of '->BillMonth' must point to class/struct/union
Error executing cl.exe.

 I hope thats what you wanted.  Thanks.
0
 
bastibartelCommented:
You tried to pass  'CBillMonth->BillMonth' as argument, do you mean BillMonth->BillMonth.
Because CBillMonth is the class name, not the variable name.


Sebastian
0
 
RoshiniAuthor Commented:
How should I pass the BillMonth in VstSetLineDetail API call?
0
 
bastibartelCommented:
That depends on the prototype of the function VstSetLineDetail(); which I do not know.
If it is:
VstSetLineDetail(/*other arguments*/, CString & BillMonth, /*other arguments*/);
then:

CBillMonth*   BillMonth = new CBillMonth(&cb);

     try
     {
          BillMonth->Open(CRecordset::snapshot, "{call AspGetTransDate}",
               CRecordset::readOnly | CRecordset::useMultiRowFetch);
     }

/* ... */
 
VstSetLineDetail(taxHdl, /* other arguments */, BillMonth->BillMonth, .... )
//###############

Try that and let me see the compiler response
Cheers,
Sebastian


0
 
RoshiniAuthor Commented:
I did tried that way, I was getting the following compiler error:

VstSetLineDetail(taxHdl, /* other arguments */, BillMonth->BillMonth, .... )



Compiling...
CalcTax.cpp
C:\CalcTax\CalcTax.cpp(793) : error C2065: 'BillMonth' : undeclared identifier
C:\CalcTax\CalcTax.cpp(793) : error C2227: left of '->BillMonth' must point to class/struct/union
Error executing cl.exe.
0
 
bastibartelCommented:
Please post the whole function in which you are using BillMonth and the API call.
1:1 leave nothing out. ;-)

Cheers,
Sebastian
0
 
mahesh1402Commented:
>>C2065: 'BillMonth' : undeclared identifier

How you declared BillMonth...
''''Make sure 'BillMonth' is CRecordSet type or CRecordSet derived class'''


-MAHESH
0
 
RoshiniAuthor Commented:
I did it already.  
0
 
mahesh1402Commented:
also post prototype of VstSetLineDetail() function as it seems to be custom function ..
0
 
bastibartelCommented:
:-) No you didn't

We need a basic compilation unit here, sometime referred to as a 'Function'
it is comprised of, but not limited to :

1. the function name,
2. opening '{'
3. some code and ..
4. closing '}'

Cheere,
Sebastian


0
 
RoshiniAuthor Commented:
VstSetLineDetail() is a vertex tax calculation functions which we call in our application.  In that function one of the parameter they passed as NULL for date which takes system date when its running.  I needed to get the date value(foreg: 20060701) from the sql database with stored procedure and pass that value into that function.  With stored procedure I am getting and passing it in BillMonth variable.  I created CRecordset class etc.  But still getting compilier errors.  Thanks.
0
 
mahesh1402Commented:
so whats prototype of VstSetLineDetail() ? and what type of 2nd parameter your supposed to pass to this function ? is it of type CRecordSet or any other ??

-MAHESH
0
 
RoshiniAuthor Commented:
In NULL for system date place I should get BillMonth value from Stored procedure and pass on to VstSetLineDetail() API function.  I hope you understand.  Instead of CRecordset type is any other way I get and pass ?
0
 
mahesh1402Commented:
>>I should get BillMonth value from Stored procedure and pass on to

It seems you need output value from your stored procedure it that case following KB article may be of your use :

How to retrieve the output parameters from a stored procedure in MFC
http://support.microsoft.com/kb/q183001/

-MAHESH
0
 
RoshiniAuthor Commented:
Since I didn't get a chance to work on the project but I learnt a lot from the help.  I would like to split points to both of them for 75 each as they get back to me asap.  Thanks.
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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 11
  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now