Parametrized store proc with Visual C++ 6.0

I'm trying to call a stored procedure with Microsoft SQL Server 7.0 and MDAC 2.1 with ADO.

I create my parametre after I add it to the parameter collection of the ADO::command and i call command::Execute methode. I have the answer "no data for one or multiple parameters"

Any body have a good example of the usage of stored procedure with ADO in Visual C++ 6.0. Not in fuc.... VB
rstamandAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rsjttyCommented:
rstamand,
i know how frustrating it is. iam in the process of doing the same.

most of the books i reffered to want to tell you how wonderful UDA & ADO is and how they are going to change the way we look at data. well, i don't need that crap, just gimme a couple of simple examples to illustrate a couple of fundamental things.

please keep me posted if you find any thing useful & i'll do the likewise.
thanks rsjtty
rsjetty@yahoo.com
0
rsjttyCommented:
rstamand,
check out the following, it's got plenty of examples

http://msdn.microsoft.com/isapi/msdnlib.idc?theURL=/library/psdk/dasdk/mdap3cab.htm


rsjetty
0
rsjttyCommented:
rstamand,
check out this example.
rsjtty

/****************************************************/
//Append and CreateParameter Methods Example (VC++)

//This example uses the Append and CreateParameter methods to execute a stored procedure with an input parameter.

#import "C:\Program Files\Common Files\System\ADO\msado15.dll" \
    no_namespace rename("EOF", "EndOfFile")

#include "conio.h"
#include "AppendX.h"

//Function declaration
inline void TESTHR(HRESULT x) {if FAILED(x) _com_issue_error(x);};
void AppendX(void);
void PrintProviderError(_ConnectionPtr pConnection);

/* description of the Stored Procedure
***
CREATE PROCEDURE byroyalty @percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @percentage
***
End Stored Procedure*/

void main()
{
    HRESULT  hr = S_OK;

    if(FAILED(::CoInitialize(NULL)))
        return;

    AppendX();
   
    //Wait here for the user to see the output.
    printf("\n\nPress any key to continue...");
    getch();

    ::CoUninitialize();
}

void AppendX(void)
{
    HRESULT hr = S_OK;

    // Define ADO object pointers.
    // Initialize pointers on define.
    // These are in the ADODB::  namespace.
    _RecordsetPtr pRstByRoyalty = NULL;
    _RecordsetPtr pRstAuthors = NULL;  
    _CommandPtr   pcmdByRoyalty = NULL;
    _ParameterPtr pprmByRoyalty = NULL;
    _ConnectionPtr pConnection = NULL;

    //Define Other variables
    IADORecordBinding   *picRs = NULL;  //Interface Pointer declared.
    CEmployeeRs emprs;                 //C++ class object

    _bstr_t strCnn("Provider=sqloledb;Data Source=srv;"
        "Initial Catalog=pubs;User Id=sa;Password=;");

    _bstr_t strMessage, strAuthorID;
    int intRoyalty;
    VARIANT vtRoyalty;

    try
    {
        //Open a Connection.
        TESTHR(hr = pConnection.CreateInstance(__uuidof(Connection)));
        hr = pConnection->Open(strCnn,"","",NULL);
        pConnection->CursorLocation = adUseClient;

        //Open Command Object with one Parameter
        TESTHR(hr = pcmdByRoyalty.CreateInstance(__uuidof(Command)));
        pcmdByRoyalty->CommandText = "byroyalty";
        pcmdByRoyalty->CommandType = adCmdStoredProc;

        //Get parameter value and append parameter
        printf("Enter Royalty: ");
        scanf("%d",&intRoyalty);

        //Define Integer/variant.
        vtRoyalty.vt = VT_I2;
        vtRoyalty.iVal = intRoyalty;

        pprmByRoyalty = pcmdByRoyalty->CreateParameter("percentage", adInteger, adParamInput, sizeof(int), vtRoyalty);
        pcmdByRoyalty->Parameters->Append(pprmByRoyalty);
        pprmByRoyalty->Value  = vtRoyalty;

        //Create Recordset by executing the command
        pcmdByRoyalty->ActiveConnection = pConnection;
        pRstByRoyalty = pcmdByRoyalty->Execute(NULL, NULL, adCmdStoredProc);

        //Open the authors table to get author names for display
        TESTHR(hr = pRstAuthors.CreateInstance(__uuidof(Recordset)));

        //You have to explicitly pass the default Cursor type and
        //LockType to the Recordset here.
        hr = pRstAuthors->Open("authors", _variant_t((IDispatch*)pConnection, true), adOpenForwardOnly, adLockReadOnly, adCmdTable);

        //Open an IADORecordBinding interface pointer which we'll
        //use for Binding Recordset to a class.
        TESTHR(hr = pRstAuthors->QueryInterface(__uuidof(IADORecordBinding), (LPVOID*)&picRs));

        //Bind the Recordset to a C++ Class here    
        TESTHR(hr = picRs->BindToRecordset(&emprs));

        //Print current data in the recordset, adding
        //author names from author table.
        printf("Authors with %d percent royalty ",intRoyalty);

        while(!(pRstByRoyalty->EndOfFile))
        {
            strAuthorID = pRstByRoyalty->Fields->Item["au_id"]->Value;
            pRstAuthors->Filter = "au_id = '"+strAuthorID+"'";

            printf("\n"  "%s, %s  %s",
                emprs.lau_idStatus == adFldOK ?
                emprs.m_szau_id : "",
                emprs.lau_fnameStatus == adFldOK ?
                emprs.m_szau_fname : "",
                emprs.lau_lnameStatus == adFldOK ?
                emprs.m_szau_lname : "");
            pRstByRoyalty->MoveNext();
        }

        //Release the IADORecordset Interface here  
        if (picRs)
            picRs->Release();

        pRstByRoyalty->Close();
        pRstAuthors->Close();
        pConnection->Close();
    }

    catch(_com_error &e)
    {
        _bstr_t bstrSource(e.Source());
        _bstr_t bstrDescription(e.Description());

        PrintProviderError(pConnection);

        printf("\n Source : %s \n Description : %s \n",
            (LPCSTR)bstrSource,(LPCSTR)bstrDescription);
    }
}

void PrintProviderError(_ConnectionPtr pConnection)
{
    // Print Provider Errors from Connection object.
    // pErr is a record object in the Connection's Error collection.
    ErrorPtr    pErr  = NULL;
    long      nCount  = 0;    
    long      i     = 0;

    if( (pConnection->Errors->Count) > 0)
    {
        nCount = pConnection->Errors->Count;
        // Collection ranges from 0 to nCount -1.
        for(i = 0; i < nCount; i++)
        {
            pErr = pConnection->Errors->GetItem(i);
            printf("Error number: %x\n Error Description: %s\n", pErr->Number, (LPCSTR)pErr->Description);
        }
    }
}

AppendX.h:

#include "icrsint.h"

//This Class extracts only author id,fname,lastname    

class CEmployeeRs : public CADORecordBinding
{
BEGIN_ADO_BINDING(CEmployeeRs)
    //Column au_id is the 1st field in the recordset  
    ADO_VARIABLE_LENGTH_ENTRY2(1, adVarChar, m_szau_id, sizeof(m_szau_id), lau_idStatus, TRUE)
    ADO_VARIABLE_LENGTH_ENTRY2(2, adVarChar, m_szau_lname, sizeof(m_szau_lname), lau_lnameStatus, TRUE)
    ADO_VARIABLE_LENGTH_ENTRY2(3, adVarChar, m_szau_fname, sizeof(m_szau_fname), lau_fnameStatus, TRUE)
END_ADO_BINDING()
public:
    CHAR m_szau_id[20];
    ULONG lau_idStatus;

    CHAR m_szau_fname[40];
    ULONG lau_fnameStatus;

    CHAR   m_szau_lname[40];
    ULONG  lau_lnameStatus;
};

) 1999 Microsoft Corporation. All rights reserved. Terms of use.
/****************************************************/
0
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

basantCommented:
Is there problem only with stored procedure or in SQL Queries too.
0
rstamandAuthor Commented:
The previous sample work fine to call the stored procedure but if the stored procedure return specific value like

---------------------------------
CREATE PROCEDURE [sp_Toto]
      @UnInt  Integer,
      @UnIntOut Integer OUTPUT
AS
      set @UnIntOut = 10
return @UnIntOut

---------------------------------

the output parameter is always NULL value and i don't know how retreive this value.
0
chensuCommented:
HOWTO: Invoke a Stored Procedure w/ADO Query using VBA/C++/Java
http://support.microsoft.com/support/kb/articles/Q185/1/25.ASP

Adovcsp.exe Demonstrates Using Stored Procedures with ADO
http://support.microsoft.com/support/kb/articles/Q184/9/68.ASP
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
basantCommented:
What I am not sure is :
If ADO generates Recordsetset via Query inside the stored procedure and as well as return the value too. What will be the value in Recordset.

0
chensuCommented:
The return value will be the first item of the Parameters collection object. You must close the recordset before accessing the return value.
0
rstamandAuthor Commented:
Finaly, I have found the solution.

My stored procedure don't return recordset then, to obtain the output value I should call the execute like that
----- Example # 1 -------------
m_Commande->Execute(&vtEmpty, &vtEmpty2, adCmdStoredProc);
-------------------------------

Do not use the assignement of the recordset like

----- Example # 2-------------
prec = m_commande->Execute (....
-------------------------------

In the example # 2, the output parameters stay empty.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C++

From novice to tech pro — start learning today.