Solved

calling stored procedures ms sql server in visual c++

Posted on 2006-07-11
31
2,065 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
  • 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
repeateFront java challenge 31 87
wait notify demo infinite loop 3 81
haveThree challenge 22 101
mapBully challenge 6 92
Introduction: Dynamic window placements and drawing on a form, simple usage of windows registry as a storage place for information. Continuing from the first article about sudoku.  There we have designed the application and put a lot of user int…
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
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.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now