Wrox Programmer Forums
Go Back   Wrox Programmer Forums > C# and C > C# 2005 > C# 2005
|
C# 2005 For discussion of Visual C# 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the C# 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 3rd, 2007, 03:15 PM
Authorized User
 
Join Date: Dec 2007
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default Parameter object malfunction - out parameter

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);
 
Old December 3rd, 2007, 03:24 PM
samjudson's Avatar
Friend of Wrox
 
Join Date: Aug 2007
Posts: 2,128
Thanks: 1
Thanked 189 Times in 188 Posts
Default

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 -/
 
Old December 3rd, 2007, 03:35 PM
Authorized User
 
Join Date: Dec 2007
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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;
        }


    }








Quote:
quote:Originally posted by samjudson
 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 -/
dev(1);
 
Old December 3rd, 2007, 03:39 PM
samjudson's Avatar
Friend of Wrox
 
Join Date: Aug 2007
Posts: 2,128
Thanks: 1
Thanked 189 Times in 188 Posts
Default

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 -/
 
Old December 3rd, 2007, 03:53 PM
Authorized User
 
Join Date: Dec 2007
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






Quote:
quote:Originally posted by samjudson
 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 -/
dev(1);
 
Old December 3rd, 2007, 03:56 PM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
 
Old December 4th, 2007, 12:58 PM
Authorized User
 
Join Date: Dec 2007
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Sam!

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






Quote:
quote:Originally posted by samjudson
 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 -/
dev(1);





Similar Threads
Thread Thread Starter Forum Replies Last Post
Session Parameter with Custom Object tna55 ASP.NET 2.0 Basics 8 February 6th, 2007 09:57 AM
Dtabase function in command parameter object? jdang67 ADO.NET 0 October 7th, 2005 10:07 AM
Problem in Parameter Object Lalit Pradhan ADO.NET 3 July 9th, 2005 01:51 PM
How to get request object parameter vikas verma Pro JSP 0 December 22nd, 2004 12:52 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.