Pass parameters for Stored procedure in VC++

[Joseph Lu]

Hi, all
I have a stored procedure created in SQL Server like the following
lines.

// stored proceudre code starts here
CREATE PROCEDURE sp_insertdata

@strdata varchar(250) ,
@rsult BIT OUTPUT ,
@erridx CHAR(7) OUTPUT
AS
BEGIN
... ...
END

GO
// stored proceudre code ends here

I want to call this stored procedure in VC++, and my C++ codes as
below:
... ...
m_pCommand->ActiveConnection = m_pConnection;
m_pCommand->CommandText="sp_insertdata";
m_pCommand->CommandType=adCmdStoredProc;
m_pCommand->Parameters->Refresh();
... ...
My question is how to define and pass parameters to the stored
procedure in VC++, Could anyone give me a reply, thanks in advance!

Joseph

[Vladimir Nesterovsky]

Hello,

I have a stored procedure created in SQL Server like the following
lines.

....

I want to call this stored procedure in VC++, and my C++ codes as
below:

....

My question is how to define and pass parameters to the stored
procedure in VC++, Could anyone give me a reply, thanks in advance!

This is one possible implementation:

// Input parameter.
_bstr_t strdata = L"data";

ADODB::_CommandPtr command = ADODB::_CommandPtr(ADODB::CLSID_Command);

command->CommandType = ADODB::adCmdStoredProc;
command->ActiveConnection = connection;

// SQL Server help advices against sp_ prefix in the stored procedure name.
command->CommandText = L"sp_insertdata";

ADODB::_ParameterPtr strdataParameter = command->CreateParameter(
L"@strdata",
ADODB::adBSTR,
ADODB::adParamInput,
strdata.length(),
strdata);

command->Parameters->Append(strdataParameter);

ADODB::_ParameterPtr rsultParameter = command->CreateParameter(
L"@rsult",
ADODB::adBoolean,
ADODB::adParamOutput,
1,
false);

command->Parameters->Append(rsultParameter);

ADODB::_ParameterPtr erridxParameter = command->CreateParameter(
L"@erridx",
ADODB::adChar,
ADODB::adParamOutput,
7,
L"");

command->Parameters->Append(erridxParameter);

_variant_t recordsAffected = long(0);

// Result record sets.
ADODB::_RecordsetPtr recordSet =
command->Execute(&recordsAffected, 0, ADODB::adOptionUnspecified);

// Output parameters.
bool rsult = rsultParameter->Value;
_bstr_t erridx = erridxParameter->Value;

P.S. The code is not compiled.
--
Vladimir Nesterovsky

[Joseph]

Yes, It work perfectly, thanks Vladimir!!!
I have a new question : because strdata includes Big5 characters , the SQL
Server side procedure can not work perfectly while it can work well when I
set strdata to strings without Big5 characters, could you tell me why?

Thanks again!

Joseph
"Vladimir Nesterovsky" <vl******@nesterovsky-bros.comдÈëÏûÏ¢
news:Oc**************@TK2MSFTNGP03.phx.gbl...

Hello,

> I have a stored procedure created in SQL Server like the following
lines.

...

> I want to call this stored procedure in VC++, and my C++ codes as
below:

...

> My question is how to define and pass parameters to the stored
procedure in VC++, Could anyone give me a reply, thanks in advance!

This is one possible implementation:

// Input parameter.
_bstr_t strdata = L"data";

ADODB::_CommandPtr command = ADODB::_CommandPtr(ADODB::CLSID_Command);

command->CommandType = ADODB::adCmdStoredProc;
command->ActiveConnection = connection;

// SQL Server help advices against sp_ prefix in the stored procedure
name.
command->CommandText = L"sp_insertdata";

ADODB::_ParameterPtr strdataParameter = command->CreateParameter(
L"@strdata",
ADODB::adBSTR,
ADODB::adParamInput,
strdata.length(),
strdata);

command->Parameters->Append(strdataParameter);

ADODB::_ParameterPtr rsultParameter = command->CreateParameter(
L"@rsult",
ADODB::adBoolean,
ADODB::adParamOutput,
1,
false);

command->Parameters->Append(rsultParameter);

ADODB::_ParameterPtr erridxParameter = command->CreateParameter(
L"@erridx",
ADODB::adChar,
ADODB::adParamOutput,
7,
L"");

command->Parameters->Append(erridxParameter);

_variant_t recordsAffected = long(0);

// Result record sets.
ADODB::_RecordsetPtr recordSet =
command->Execute(&recordsAffected, 0, ADODB::adOptionUnspecified);

// Output parameters.
bool rsult = rsultParameter->Value;
_bstr_t erridx = erridxParameter->Value;

P.S. The code is not compiled.
--
Vladimir Nesterovsky

[Joseph]

I got it ,it is SQL server side issue, thanks to all for your help !!!

Joseph

"Joseph" <jo******@yahoo.comдÈëÏûÏ¢
news:OZ**************@TK2MSFTNGP05.phx.gbl...

Yes, It work perfectly, thanks Vladimir!!!
I have a new question : because strdata includes Big5 characters , the SQL
Server side procedure can not work perfectly while it can work well when I
set strdata to strings without Big5 characters, could you tell me why?

Thanks again!

Joseph
"Vladimir Nesterovsky" <vl******@nesterovsky-bros.comдÈëÏûÏ¢
news:Oc**************@TK2MSFTNGP03.phx.gbl...

>Hello,

>> I have a stored procedure created in SQL Server like the
following
lines.

...

>> I want to call this stored procedure in VC++, and my C++ codes as
below:

...

>> My question is how to define and pass parameters to the stored
procedure in VC++, Could anyone give me a reply, thanks in advance!

This is one possible implementation:

// Input parameter.
_bstr_t strdata = L"data";

ADODB::_CommandPtr command = ADODB::_CommandPtr(ADODB::CLSID_Command);

command->CommandType = ADODB::adCmdStoredProc;
command->ActiveConnection = connection;

// SQL Server help advices against sp_ prefix in the stored procedure
name.
command->CommandText = L"sp_insertdata";

ADODB::_ParameterPtr strdataParameter = command->CreateParameter(
L"@strdata",
ADODB::adBSTR,
ADODB::adParamInput,
strdata.length(),
strdata);

command->Parameters->Append(strdataParameter);

ADODB::_ParameterPtr rsultParameter = command->CreateParameter(
L"@rsult",
ADODB::adBoolean,
ADODB::adParamOutput,
1,
false);

command->Parameters->Append(rsultParameter);

ADODB::_ParameterPtr erridxParameter = command->CreateParameter(
L"@erridx",
ADODB::adChar,
ADODB::adParamOutput,
7,
L"");

command->Parameters->Append(erridxParameter);

_variant_t recordsAffected = long(0);

// Result record sets.
ADODB::_RecordsetPtr recordSet =
command->Execute(&recordsAffected, 0, ADODB::adOptionUnspecified);

// Output parameters.
bool rsult = rsultParameter->Value;
_bstr_t erridx = erridxParameter->Value;

P.S. The code is not compiled.
--
Vladimir Nesterovsky