Solved

calling stored procedures ms sql server in visual c++

Posted on 2006-07-11
31
2,175 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
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
cat dog challenge 18 127
mixString challenge 36 132
move a line in eclipse 3 107
Annoying "thing" blocks my view 4 117
This is to be the first in a series of articles demonstrating the development of a complete windows based application using the MFC classes.  I’ll try to keep each article focused on one (or a couple) of the tasks that one may meet.   Introductio…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
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.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

828 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