Hi !

I designed a stored procedure in sql server 2000 & checked it from SQL server 2000 itself. it works fine. It has some in parameters as well as some out parameters.

The problem is that when i call the stored procedure from c# using ado.net and parameter collection object, my out parameter is not populated with return value. I checked the procedure within SQL server it works fine returning values. But my dot net parameter object somehow misses it. I just cant figure it out why ?

again sql server supports 2 types of parameters in & out. But parameter object supports 3 types of parameters in,out & in&out.

How do you use the 3rd type ?

Thanks in advance.


dev(1);

__________________
dev(1);

Output parameters and Return values are dfferent things.

I think an InputOutput parameter can just be used like an output parameter, except you can also pass in an initial value.

Show us some code that is not working an we might be able to help better...

/- Sam Judson : Wrox Technical Editor -/

HI !!

Here is my code . a bit longer. i hope you dont mind. Here none of my output parameters contain any return value after execution.

================================================== =============

  protected void Button3_Click(object sender, EventArgs e)
    {// add product
        SqlConnection cn2 = new SqlConnection("user id=sa;password=;database=das");
        SqlCommand c2 = new SqlCommand("UPDTRAN ", cn2);
        c2.CommandType = CommandType.StoredProcedure;

        SqlParameter parameter =c2.Parameters.Add("@VCHRNO", SqlDbType.Decimal,5);
        parameter.Direction = ParameterDirection.InputOutput;

        if (Label9.Text == "")
        { //add a new invoice
        parameter.Value=(decimal) -1;
        }
        else
        //add in to last invoice
        {
            parameter.Value= Convert.ToDecimal(Label9.Text);
        }

        parameter = c2.Parameters.Add("@SLNO", SqlDbType.Decimal);
        parameter.Direction = ParameterDirection.InputOutput;
        if (Label13.Text == "")
            Label13.Text = "0";
        parameter.Value = Convert.ToDecimal(Label13.Text);


        parameter = c2.Parameters.Add("@TDATE", SqlDbType.DateTime);
        parameter.Direction = ParameterDirection.Input;
        parameter.Value = Convert.ToDateTime(d2.SelectedItem.Text + "-" + d1.SelectedItem.Text + "-" + d3.SelectedItem.Text);
        //parameter.Value = d2.SelectedItem.Text + "-" + d1.SelectedItem.Text + "-" + d3.SelectedItem.Text;

        parameter = c2.Parameters.Add("@SLCODE", SqlDbType.Decimal);
        parameter.Direction = ParameterDirection.Input;
        parameter.Value = Convert.ToDecimal(dl2.SelectedItem.Value.ToString( ));

        parameter = c2.Parameters.Add("@TAMT", SqlDbType.Decimal);
        parameter.Direction = ParameterDirection.InputOutput;
        parameter.Value = Convert.ToDecimal(Label12.Text);

        parameter = c2.Parameters.Add("@TTYPE", SqlDbType.Char);
        parameter.Direction = ParameterDirection.Input;
        parameter.Value = Convert.ToChar(dl1.SelectedIndex);

        parameter = c2.Parameters.Add("@DC", SqlDbType.Decimal);
        parameter.Direction = ParameterDirection.Input;
        parameter.Value =(decimal) -1;

        parameter = c2.Parameters.Add("@PCODE", SqlDbType.Decimal);
        parameter.Direction = ParameterDirection.Input;
        parameter.Value = Convert.ToDecimal(dl3.SelectedValue.ToString());

        parameter = c2.Parameters.Add("@QTY", SqlDbType.Decimal);
        parameter.Direction = ParameterDirection.Input;
        parameter.Value = Convert.ToDecimal(t1.Text);

        parameter = c2.Parameters.Add("@RATE", SqlDbType.Decimal);
        parameter.Direction = ParameterDirection.Input;
        parameter.Value = Convert.ToDecimal(t2.Text);

        parameter = c2.Parameters.Add("@TAX", SqlDbType.Decimal);
        parameter.Direction = ParameterDirection.Input;
        parameter.Value = Convert.ToDecimal(t4.Text);

        parameter = c2.Parameters.Add("@DSCNT", SqlDbType.Decimal);
        parameter.Direction = ParameterDirection.Input;
        parameter.Value = Convert.ToDecimal(t3.Text);

        Label14.Text = c2.Parameters.Count.ToString() + ","+c2.Parameters["@SLNO"].Value.ToString() +","+ c2.Parameters["@VCHRNO"].Value.ToString();

        try
        {
            cn2.Open();
            int dr;
            dr = c2.ExecuteNonQuery();
             Label13.Text=c2.Parameters["@SLNO"].Value.ToString();
             Label9.Text = c2.Parameters["@VCHRNO"].Value.ToString();
             Label15.Text = c2.Parameters.Count.ToString() + c2.Parameters["@SLNO"].Value.ToString() + c2.Parameters["@VCHRNO"].Value.ToString();

        }
        catch (SqlException se)
        {
            LL.Text=se.Message;
        }


    }








dev(1);

Well I'd recommend changing them to ParameterDirection.Outut, and see if that works. Otherwise I think it looks fine so the problem might be in your stored procedure.

/- Sam Judson : Wrox Technical Editor -/

Here is my stored procedure. I hope this will help.

================================================== ====
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[UPDTRAN]
    -- Add the parameters for the stored procedure here
    (@VCHRNO NUMERIC(5,0) OUTPUT, @SLNO NUMERIC(2,0) OUTPUT,@TDATE DATETIME ,
    @SLCODE NUMERIC(6,0),@TAMT NUMERIC(8,2)OUTPUT,@TTYPE CHAR(1),@DC NUMERIC(2,0),
    @PCODE NUMERIC(6,0),@QTY NUMERIC(5),@RATE NUMERIC(8,2), @TAX NUMERIC(8,2),
    @DSCNT NUMERIC(8,2))
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
IF @VCHRNO<=0
BEGIN -- ADD NEW RECORD IN TRANS
SET @TAMT=(@QTY*@RATE)-@DSCNT+@TAX;
  --DECLARE @DDT AS DATETIME;
  --SET @DDT=CONVERT(DATETIME,@TDATE,110);
  --INSERT INTO TRANS (TDATE,SLCODE,TAMT,TTYPE,DC) VALUES(CAST(@TDATE AS DATETIME),@SLCODE ,
  INSERT INTO TRANS (TDATE,SLCODE,TAMT,TTYPE,DC) VALUES(@TDATE,@SLCODE ,
  @TAMT,@TTYPE,@DC);

  SELECT @VCHRNO=MAX(VCHRNO) FROM TRANS;
  SET @SLNO=1;
  INSERT INTO TRANS2 (VCHRNO,SLNO,PCODE,QTY,RATE,TAX,DSCNT) VALUES(@VCHRNO,@SLNO,@PCODE,@QTY,@RATE,@TAX,@DSCNT );
  UPDATE PRODCT SET STOCK=STOCK-@QTY WHERE PCODE=@PCODE;
END
ELSE
 BEGIN
SET @SLNO=@SLNO+1;
  INSERT INTO TRANS2 (VCHRNO,SLNO,PCODE,QTY,RATE,TAX,DSCNT) VALUES(@VCHRNO,@SLNO,@PCODE,@QTY,@RATE,@TAX,@DSCNT );
  SELECT @TAMT=SUM((@QTY*@RATE)-@DSCNT+@TAX) FROM TRANS2 WHERE VCHRNO=@VCHRNO ;
  UPDATE TRANS SET TAMT=@TAMT WHERE VCHRNO=@VCHRNO;
  UPDATE PRODCT SET STOCK=STOCK-@QTY WHERE PCODE=@PCODE;
 END

DECLARE @K AS NUMERIC(8,0);
SELECT @k=STOCK FROM PRODCT ;

IF @K<0
    ROLLBACK TRANSACTION;
ELSE
    COMMIT TRANSACTION;
    -- Insert statements for procedure here
END






dev(1);

Did you change the ParameterDirection as Sam suggested? What was the result?

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor :.
Wrox Books 24 x 7
================================================== =========

Hi Sam!

Thanks for the tip. I changed InOut parameters to Out parameter type. Now my app works fine. thank you very much.






dev(1);