SqlCommand Return and Output Parameters Not Working, but Input does?

Hot Topic

Will package work on different CTP version?
Two issues with SSIS script
Keep getting Virtual Memory errors
find primary and foreign keys
Conversation ID cannot be associated with an active conversation
SqlCeReplication for merge replication
Cannot connect to Database in Server Management Studio
???msn
Minimum Hardware Requirement Warning
cant see Report manager page

SQL Server

Error when trying to create "datasources"
Cannot attached Personal.mdf
Invalid Descriptor Index calling SQLGetData
Modify SSIS package saved on server
Diagram to Image
Add a DateLoaded column to input data
Configure TCP/IP port during unattended instance installation
System.String to SqlTypes.SqlXml
Event notification and Service Broker
Enterprise Publisher to Express Subscribers (2005)

SqlCommand Return and Output Parameters Not Working, but Input does?

The foolowing code I cannot seem to get working right. There is an open connection c0 and a SqlCommand k0 persisting in class.The data in r0 is correct and gets the input arguments at r0=k0->ExecuteReader(), but nothing I do seems to get the output values. What am I missing about this

System::Boolean rs::sp(System::String ^ ssp){

System::String ^ k0s0; bool bOK;

System::Data::SqlClient::SqlParameter ^ parami0;

System::Data::SqlClient::SqlParameter ^ parami1;

System::Data::SqlClient::SqlParameter ^ parami2;

System::Data::SqlClient::SqlParameter ^ paramz0;

System::Data::SqlClient::SqlParameter ^ paramz1;

System::Int32 pz0=0;System::Int32 pz1=0;

k0s = ssp;

k0->CommandType=System::Data::CommandType::StoredProcedure;

k0->CommandText=k0s;

paramz0=k0->Parameters->Add("@RETURN_VALUE", System::Data::SqlDbType::Int);

//paramz0=k0->Parameters->AddWithValue("@RETURN_VALUE",pz0);

//paramz0=k0->Parameters->AddWithValue("@RETURN_VALUE",pz0);

paramz0->Direction=System::Data::ParameterDirection::ReturnValue;

paramz0->DbType=System::Data::DbType::Int32;

parami0=k0->Parameters->AddWithValue("@DESCXV","chicken");

parami0->Direction=System::Data::ParameterDirection::Input;

parami1=k0->Parameters->AddWithValue("@SRCXV","UU");

parami1->Direction=System::Data::ParameterDirection::Input;

//paramz1=k0->Parameters->AddWithValue("@RCOUNT",pz1);

paramz1=k0->Parameters->Add("@RCOUNT",System::Data::SqlDbType::Int);

paramz1->Direction=System::Data::ParameterDirection::InputOutput;

paramz0->DbType=System::Data::DbType::Int32;

//k0->Parameters->GetParameter("@RCOUNT");

r0=k0->ExecuteReader();

//pz0=System::Convert::ToInt32(paramz0->SqlValue);

bOK=k0->Parameters->Contains("@RCOUNT");

//k0->Parameters->GetParameter("@RCOUNT");

pz0=System::Convert::ToInt32(paramz0->Value);

pz1=System::Convert::ToInt32(paramz1->Value);

ndx = -1;

while(r0->Read()){

if (ndx == -1){

ndx=0;

pai0ndx=0;

pad0ndx=0;

r0nf=r0->FieldCount::get();

for (iG1_20=0;iG1_20<r0nf;iG1_20++){

this->psf0[iG1_20]=this->r0->GetName(iG1_20);

this->psv0[iG1_20]=this->r0->GetDataTypeName(iG1_20);

this->psz0[iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));

this->pas0[ndx,iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));

if (psv0[iG1_20]=="int") {pai0[ndx,pai0ndx]=System::Convert::ToInt32(r0->GetValue(iG1_20));pai0ndx++;}

if (psv0[iG1_20]=="float") {pad0[ndx,pad0ndx]=System::Convert::ToDouble(r0->GetValue(iG1_20));pad0ndx++;}

}

}

else {

pai0ndx=0;

pad0ndx=0;

for (iG1_20=0;iG1_20<r0nf;iG1_20++)

{ this->pas0[ndx,iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));

if (psv0[iG1_20]=="int") {pai0[ndx,pai0ndx]=System::Convert::ToInt32(r0->GetValue(iG1_20));pai0ndx++;}

if (psv0[iG1_20]=="float") {pad0[ndx,pad0ndx]=System::Convert::ToDouble(r0->GetValue(iG1_20));pad0ndx++;}

}

}

ndx++;

}

r0nr=ndx;

r0->Close();

k0->Parameters->Remove(paramz0);

k0->Parameters->Remove(parami1);

k0->Parameters->Remove(parami0);

k0->Parameters->Remove(paramz1);

return true;

}

Answer this question

SqlCommand Return and Output Parameters Not Working, but Input does?

  • DMTUC10

    use the information SChemas for this, these views will provide you with the appropiate information:

    INFORMATION_SCHEMA.ROUTINES --AND
    INFORMATION_SCHEMA.PARAMETERS

    HTH, Jens Suessmeyer.

  • Comanche

    Thank you. Closing the SqlDataReader let me read the returns. Now all I need to do is code this to look up all the available stored procedures in that database, get their input and output names and datatypes, generate the prompts, validate inputs, ....

    Thanks again.

  • rcat

    Hi, as the SQLdatareader is a serverside cursor, you can get the return / output values after closing the reader. Try this and see if it works. Sorry not to get through your code in detail but I am not that familiar with your syntax.

    HTH, jens Suessmeyer.

  • rrrico74

    I suggest using the INFORMATION_SCHEMA views for this, in particular

    INFORMATION_SCHEMA.ROUTINES --AND
    INFORMATION_SCHEMA.PARAMETERS

    HTH, Jens Suessmeyer.
  • SqlCommand Return and Output Parameters Not Working, but Input does?
Answer this question