Solved

calling stored procedures ms sql server in visual c++

Posted on 2006-07-11
31
2,295 Views
Last Modified: 2013-11-20
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
Comment
Question by:Roshini
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 10
  • 7
31 Comments
 
LVL 22

Accepted Solution

by:
mahesh1402 earned 125 total points
ID: 17081492
0
 

Author Comment

by:Roshini
ID: 17081535
I need the coding.  Do you see any modifications for my code.  Do I need to create the class etc.  I am confused.
0
 
LVL 22

Expert Comment

by:mahesh1402
ID: 17081647
look at articles they have required coding for your purpose....you need to create CRecordSet derived class

-MAHESH
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 22

Expert Comment

by:mahesh1402
ID: 17081704
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
 
LVL 22

Expert Comment

by:mahesh1402
ID: 17082091
also here  
BillMonth->Open(CRecordset::snapshot, "{call AspGetTransDate}",.. <== remove quotes "

try :

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

-MAHESH
0
 
LVL 22

Expert Comment

by:mahesh1402
ID: 17082113
sorry it shld be type

"{call procedure_name(params....)}"
0
 

Author Comment

by:Roshini
ID: 17082163

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

Expert Comment

by:mahesh1402
ID: 17083118
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
 
LVL 5

Assisted Solution

by:bastibartel
bastibartel earned 125 total points
ID: 17097137
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
 
LVL 22

Expert Comment

by:mahesh1402
ID: 17097302
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
 
LVL 5

Expert Comment

by:bastibartel
ID: 17097941
Sorry, CRecordset::readOnly works of course - what I meant was 'exclusive access' - My mistake
Sebastian
0
 

Author Comment

by:Roshini
ID: 17098536
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
 
LVL 5

Expert Comment

by:bastibartel
ID: 17098599
Please post the code fragments, generating these errors.

Cheers,
Sebastian
0
 

Author Comment

by:Roshini
ID: 17098654
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
 
LVL 5

Expert Comment

by:bastibartel
ID: 17099019
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
 

Author Comment

by:Roshini
ID: 17099062
How should I pass the BillMonth in VstSetLineDetail API call?
0
 
LVL 5

Expert Comment

by:bastibartel
ID: 17099094
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
 

Author Comment

by:Roshini
ID: 17099129
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
 
LVL 5

Expert Comment

by:bastibartel
ID: 17099308
Please post the whole function in which you are using BillMonth and the API call.
1:1 leave nothing out. ;-)

Cheers,
Sebastian
0
 
LVL 22

Expert Comment

by:mahesh1402
ID: 17099366
>>C2065: 'BillMonth' : undeclared identifier

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


-MAHESH
0
 

Author Comment

by:Roshini
ID: 17099382
I did it already.  
0
 
LVL 22

Expert Comment

by:mahesh1402
ID: 17099383
also post prototype of VstSetLineDetail() function as it seems to be custom function ..
0
 
LVL 5

Expert Comment

by:bastibartel
ID: 17099658
:-) 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
 

Author Comment

by:Roshini
ID: 17099724
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
 
LVL 22

Expert Comment

by:mahesh1402
ID: 17099933
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
 

Author Comment

by:Roshini
ID: 17099990
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
 
LVL 22

Expert Comment

by:mahesh1402
ID: 17106363
>>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
 

Author Comment

by:Roshini
ID: 17316964
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

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: Dialogs (2) modeless dialog and a worker thread.  Handling data shared between threads.  Recursive functions. Continuing from the tenth article about sudoku.   Last article we worked with a modal dialog to help maintain informat…
In this post we will learn different types of Android Layout and some basics of an Android App.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Suggested Courses

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question