Microsoft KB Archive/310142

= HOW TO: Execute SQL Parameterized Stored Procedures by Using the ODBC .NET Provider and Visual C++ .NET =

Article ID: 310142

Article Last Modified on 9/4/2003

-

APPLIES TO


 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft ADO.NET 1.1
 * Microsoft Visual C++ .NET 2002 Standard Edition
 * Microsoft Visual C++ .NET 2003 Standard Edition

-



This article was previously published under Q310142



For a Microsoft Visual Basic .NET version of this article, see 309486.

For a Microsoft Visual C# .NET version of this article, see 310130.

This article refers to the following Microsoft .NET Framework Class Library namespaces:
 * System::Xml
 * Microsoft::Data::Odbc

IN THIS TASK
SUMMARY
 * Call Syntax Examples
 * Test Project - Single Input Parameter
 * Test Project - Multiple Parameter Types
 * Troubleshooting

REFERENCES



SUMMARY
This step-by-step article describes how to call a parameterized Microsoft SQL Server stored procedure using the Open Database Connectivity (ODBC) .NET managed provider and Microsoft Visual C++ .NET.

Executing a parameterized stored procedure by using the ODBC .NET Provider is slightly different from executing the same procedure by using the OLE DB or SQL Sever .NET data provider. For example, with ODBC .NET Provider, you must use the ODBC CALL syntax to call the stored procedure instead of by using the name of the stored procedure. For additional information about the ODBC CALL syntax, visit the following Microsoft Developer Network (MSDN) Web site:

Procedure Calls

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcprocedure_calls.asp

back to the top

Call Syntax Examples
  Here is an example of the CALL syntax for an actual stored procedure in the Northwind sample database that expects one input parameter: {CALL CustOrderHist (?)}   Here is an example of the CALL syntax for a stored procedure that expects one input parameter and returns one output parameter and a return value. The first placeholder represents the return value: {? = CALL Procedure1 (?, ?)  The ODBC .NET managed provider, like the OLE DB .NET data provider, processes parameters by ordinal position (zero-based) and not by name.

back to the top

Test Project - Single Input Parameter
 If you have not already done so, download and install the ODBC .NET managed provider.

To download the ODBC .NET managed provider, visit the following Microsoft Web site:

http://msdn.microsoft.com/dataaccess

 In Microsoft Visual Studio .NET, create a new Microsoft Visual C++ Managed C++ Application. In Solution Explorer, double-click the source (.cpp) file.</li>  To resolve a reference to Microsoft.Data.Odbc.dll, follow these steps:

<ol style="list-style-type: lower-alpha;"> Open the Property Pages dialog box for the project.</li> Click the C/C++ folder.</li> Click the General tab.</li> Modify the Resolve #using References property to include the path for the folder that contains Microsoft.Data.Odbc.dll (for example, type C:\Program Files\Microsoft.NET\Odbc.Net\ ).</li></ol>

NOTE: Alternatively, add the path to the using statement. For example:
 * 1) using &quot;C:\Program Files\Microsoft.NET\Odbc.Net\Microsoft.Data.Odbc.dll&quot;

Note To resolve a reference to Microsoft.Data.Odbc.dll in Visual C++ .NET 2003 follow the below mentioned step

On the Project menu, click Add reference, and then double-click Microsoft.Data.ODBC.dll to add it to the selected items list. Close the References dialog box. </li>  Remove the default code from the source file, and then paste the following code in the file: using namespace System::Xml; using namespace System; using namespace System::Data; using namespace Microsoft::Data::Odbc;
 * 1) include &quot;stdafx.h&quot;
 * 2) using <mscorlib.dll>
 * 3) include <tchar.h>
 * 4) using <system.xml.dll>
 * 1) using <system.dll>
 * 1) using <system.data.dll>
 * 1) using <Microsoft.Data.Odbc.dll>

// This is the entry point for this application

int _tmain(void) {

OdbcConnection *myCon = new OdbcConnection(&quot;Driver={Sql Server};server=mySQL;trusted_connection=yes;database=northwind;&quot;); try{ myCon->Open; OdbcCommand *myCmd = new OdbcCommand(&quot;{call CustOrderHist(?)}&quot;,myCon);

//The following does not work: //OdbcCommand *myCmd = new OdbcCommand(&quot;CustOrderHist&quot;,myCon); //myCmd->CommandType=CommandType::StoredProcedure;

myCmd->Parameters->Add(&quot;CustId&quot;,OdbcType::Char,5); myCmd->Parameters->Item[0]->Value=S&quot;ALFKI&quot;; OdbcDataReader *myReader = myCmd->ExecuteReader;

while (myReader->Read) {     for(Int32 i=0;i<myReader->FieldCount;i++) { Console::WriteLine(&quot;{0}:{1}&quot;,(myReader->GetName(i))->ToString,(myReader->GetValue(i))->ToString); }     Console::WriteLine; }

myReader->Close; }

catch(OdbcException *myEx) {     for (int i=0;i<myEx->Errors->Count;i++) { Console::WriteLine(&quot;Source:{0};Message={1}&quot;,myEx->Errors->Item[i]->Source,myEx->Errors->Item[i]->Message); } }  myCon->Close; return 0; }                   </li> Change the connection string appropriately. Press CTRL+F5 to compile and run the project.

</li></ol>

Result:

This code calls the CustOrderHist stored procedure, passing in the Customer ID as a single input parameter, and returns a result set. In the Output window, you should see the list of products ordered by Northwind customer ALFKI.

back to the top

Test Project - Multiple Parameter Types
<ol>  The following stored procedure accepts a CustomerID as an input parameter, and returns:

 A list of orders that the customer placed.</li> The average freight per order that is paid by that customer as an output parameter.</li> The number of orders that the customer placed as a return value.</li></ul>

In Query Analyzer, create the following stored procedure in the Northwind sample database: CREATE PROCEDURE MultiParamSP @CustID CHAR(5), @AvgFreight MONEY OUTPUT AS SELECT @AvgFreight = AVG(Freight) FROM Orders WHERE CustomerID = @CustID SELECT * FROM Orders WHERE CustomerID = @CustID RETURN @@ROWCOUNT </li>  Repeat steps 2 through 6 in the &quot;Single Input Parameter&quot; section of this article, but use the following code: using namespace System::Xml; using namespace System; using namespace System::Data; using namespace Microsoft::Data::Odbc;
 * 1) include &quot;stdafx.h&quot;
 * 2) using <mscorlib.dll>
 * 3) include <tchar.h>
 * 4) using <system.xml.dll>
 * 1) using <system.dll>
 * 1) using <system.data.dll>
 * 1) using <Microsoft.Data.Odbc.dll>

// This is the entry point for this application int _tmain(void) { OdbcConnection *myCon = new OdbcConnection(&quot;Driver={Sql Server};server=mySQL;trusted_connection=yes;database=northwind;&quot;);

try{ myCon->Open; OdbcCommand *myCmd = new OdbcCommand(&quot;{?=call MultiParamSP(?,?)}&quot;,myCon); myCmd->Parameters->Add(&quot;RetVal&quot;,OdbcType::Int); myCmd->Parameters->Item[0]->Direction=ParameterDirection::ReturnValue; myCmd->Parameters->Add(&quot;CustId&quot;,OdbcType::Char,5); myCmd->Parameters->Item[1]->Value=S&quot;ALFKI&quot;; myCmd->Parameters->Add(&quot;AvgFr&quot;,OdbcType::Double); myCmd->Parameters->Item[2]->Direction=ParameterDirection::Output; //The following does not work: //OdbcCommand *myCmd = new OdbcCommand(&quot;MultiParamSP&quot;,myCon); //myCmd->CommandType=CommandType::StoredProcedure;

OdbcDataReader *myReader = myCmd->ExecuteReader; while (myReader->Read) {    Console::WriteLine; for(Int32 i=0;i<myReader->FieldCount;i++) { Console::WriteLine(&quot;{0}:{1}&quot;,(myReader->GetName(i))->ToString,(myReader->GetValue(i))->ToString); }  }   myReader->Close; Console::WriteLine(&quot;OutputParamVal={0};ReturnVal={1}&quot;,myCmd->Parameters->Item[2]->Value,myCmd->Parameters->Item[0]->Value); } catch(OdbcException *myEx) { for (int i=0;i<myEx->Errors->Count;i++) { Console::WriteLine(&quot;Source:{0};Message={1}&quot;,myEx->Errors->Item[i]->Source,myEx->Errors->Item[i]->Message); } } catch(System::Exception *myEx) {  Console::WriteLine(&quot;Source:{0};Message={1}&quot;,myEx->Source,myEx->Message); } myCon->Close; return 0; }                   </li></ol>

Result:

The code calls the MultiParamSP stored procedure, which you created in the &quot;Single Input Parameter&quot; section, passing in the CustomerID as a single input parameter, and returns a result set, an output parameter, and a return value. In the Output window, you should see the list of orders placed by Northwind customer ALFKI, the average freight the customer paid per order, and the count of orders.

back to the top

Troubleshooting
 You cannot use the ADO syntax that you typically use to call stored procedures with the ODBC .NET managed provider; typically, the name of the procedure alone is provided as the CommandText.</li> <li> With SQL Server driver, when a stored procedure returns a result set, the output parameters and the return value are not available until the result set has been accessed and closed. For example, if you omitted the line dr.Close in the second code sample, you cannot retrieve the values for the output parameter and the return value. </li> <li>The ODBC .NET managed provider, like the OLE DB .NET Provider, processes parameters by ordinal position (zero-based) and not by name.</li> <li>The ODBC .NET managed provider is not included with Visual Studio .NET; you have to down load it separately.</li></ul>

back to the top

<div class="references_section">