Updating field

  • Good day,

    I am having trouble with my update query.

    I want to update txtReferrence but the update value will be the return or result of the another sp which run over linked server.

    here is my code..

    ALTER PROCEDURE [dbo].[sp_UPDATEREFNUMBER]

    -- Add the parameters for the stored procedure here

    @bintDriver BIGINT

    --,@dtDeliveryDate Datetime

    ,@decTotalAmount DECIMAL(18,2)

    ,@decAmountPaid DECIMAL(18,2)

    ,@bintShipment BIGINT

    ,@intStatus INT

    ,@dtpaymentDate DATETIME

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Insert statements for procedure here

    DECLARE

    @root AS NVARCHAR(20)

    ,@dpNumber AS BIGINT

    ,@amounttobepaid AS DECIMAL(18,2)

    ,@paymentdate AS DATETIME

    --

    SET @dpNumber = (SELECT bintDriver = CONVERT(NVARCHAR,bintDriver) FROM tblDeliveryListAmount WHERE bintDriver = @bintDriver AND bintShipment = @bintShipment)

    SET @amounttobepaid = (SELECT decAmountPaid = CONVERT(NVARCHAR,decAmountPaid) FROM tblDeliveryListAmount WHERE bintDriver = @bintDriver AND bintShipment = @bintShipment)

    SET @paymentdate = (SELECT CONVERT(VARCHAR,dtDeliveryDate,101) FROM tblDeliveryListAmount WHERE bintDriver = @bintDriver AND bintShipment = @bintShipment)

    --

    EXEC @root = [dev02-kc2-bd].ar_dev.dbo.spQuery_GetSequenceNo @dpNumber, @amounttobepaid, @paymentdate

    UPDATE tblDeliveryListAmount

    --

    SET

    --decAmountPaid = @decAmountPaid

    txtReferrence = @root

    --,intStatus = '2'

    --,dtpaymentDate=getdate()

    WHERE

    bintDriver = @bintDriver

    ANDbintShipment = @bintShipment

    All i want is to update the txtReferrence with the result of the

    EXEC @root = [dev02-kc2-bd].ar_dev.dbo.spQuery_GetSequenceNo @dpNumber, @amounttobepaid, @paymentdate

    that's why I assign the txtReferrence to var @root just to update the value. but the value always return 0.

    how can i update it..>?

    thanks

  • could you please post DDL for spQuery_GetSequenceNo. I have a suspicion that is not returning number as a result. It may be doing it through output parameter...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • The RETURN value of a stored procedure is kinda reserved for the error status. 0 means no error, any other number means an error occurred. Check the stored procedure for an OUTPUT variable or result set.

    Incidentally, you can load your variables in a single statement rather than reading the same table (and row) three times:

    SELECT

    @dpNumber = CONVERT(NVARCHAR,bintDriver),

    @amounttobepaid = CONVERT(NVARCHAR,decAmountPaid),

    @paymentdate = CONVERT(VARCHAR,dtDeliveryDate,101)

    FROM tblDeliveryListAmount

    WHERE bintDriver = @bintDriver

    AND bintShipment = @bintShipment

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Good day,

    Sad to say that i dont have the previledges to view the DDL of spQuery_GetSequenceNo that given to me.

    @ChrisM@Work

    is set the value of the DPNUmber,amounttobepaid,paymentdate once the user insert data it will generate a SequenceNo based on that parameter that i had.

    I also thinking to use OUTPUT on my query just to get the value of the result of the exec SP.. but i dont have idea how could i start

    here is my code..

    here is my DAL

    protected int UpdateRefNumberDAL(DeliveryListAmountEntities _deliveryListAmount)

    {

    //SqlParameter x = new SqlParameter();

    //x.Direction = ParameterDirection.Output;

    return base.Execute("sp_UPDATEREFNUMBER", _deliveryListAmount);

    //DbCommand cmd = ("sp_UPDATEREFNUMBER");

    //cmd.Parameters["@myPAram"].Direction = ParameterDirection.Output;

    //int ax = (int)cmd.Parameters["@myPAram"].Value;

    //return ax;

    }

    here is my BLL

    public int UpdateRefNumber(DeliveryListAmountEntities _deliveryListAmount)

    {

    return base.UpdateRefNumberDAL(_deliveryListAmount);

    }

    here is my code behind

    protected void rptCash_ItemCommand(object source, RepeaterCommandEventArgs e)

    {

    if (e.CommandName == "btnAmount")

    {

    Label lblSH = (Label)e.Item.FindControl("lblShipment");

    TextBox txtAmount = (TextBox)e.Item.FindControl("txtAmountPaid");

    _deliveryListAmount.bintShipment = Convert.ToInt64(lblSH.Text);

    _deliveryListAmount.bintDriver = Convert.ToInt64(lblDPNumber.Text);

    _deliveryListAmount.decAmountPaid = Convert.ToDecimal(txtAmount.Text);

    _deliveryListAmount.dtpaymentDate = Convert.ToDateTime(txtDateOfPayment.Text);

    _deliveryListAmount.decTotalAmount = Convert.ToDecimal(txtTotalAmountPaid.Text);

    if (_collectBankBll.UpdateRefNumber(_deliveryListAmount) != 0)

    {

    GetCashCollection(_deliveryListAmount);

    }

    }

    }

    and also my DAL Connection

    public abstract class DALConnection

    {

    internal string GetAppConnectionString()

    {

    return ConfigurationManager.ConnectionStrings[ConfigurationSettings.AppSettings["DefaultConnection"]].ConnectionString;

    }

    internal string GetAppConnectionString(string ConnectionName)

    {

    return ConfigurationManager.ConnectionStrings[ConnectionName].ConnectionString;

    }

    internal int GetConnectionTimeout()

    {

    string strConnectionTimeout = Convert.ToString(ConfigurationSettings.AppSettings["ConnectionTimeout"]);

    if (strConnectionTimeout.Trim() == string.Empty)

    return 30;

    else

    return int.Parse(strConnectionTimeout);

    }

    }

  • cyberdaemon (5/10/2013)


    Good day,

    Sad to say that i dont have the previledges to view the DDL of spQuery_GetSequenceNo that given to me.

    @ChrisM@Work

    is set the value of the DPNUmber,amounttobepaid,paymentdate once the user insert data it will generate a SequenceNo based on that parameter that i had.

    I also thinking to use OUTPUT on my query just to get the value of the result of the exec SP.. but i dont have idea how could i start

    here is my code..

    here is my DAL

    protected int UpdateRefNumberDAL(DeliveryListAmountEntities _deliveryListAmount)

    {

    //SqlParameter x = new SqlParameter();

    //x.Direction = ParameterDirection.Output;

    return base.Execute("sp_UPDATEREFNUMBER", _deliveryListAmount);

    //DbCommand cmd = ("sp_UPDATEREFNUMBER");

    //cmd.Parameters["@myPAram"].Direction = ParameterDirection.Output;

    //int ax = (int)cmd.Parameters["@myPAram"].Value;

    //return ax;

    }

    here is my BLL

    public int UpdateRefNumber(DeliveryListAmountEntities _deliveryListAmount)

    {

    return base.UpdateRefNumberDAL(_deliveryListAmount);

    }

    here is my code behind

    protected void rptCash_ItemCommand(object source, RepeaterCommandEventArgs e)

    {

    if (e.CommandName == "btnAmount")

    {

    Label lblSH = (Label)e.Item.FindControl("lblShipment");

    TextBox txtAmount = (TextBox)e.Item.FindControl("txtAmountPaid");

    _deliveryListAmount.bintShipment = Convert.ToInt64(lblSH.Text);

    _deliveryListAmount.bintDriver = Convert.ToInt64(lblDPNumber.Text);

    _deliveryListAmount.decAmountPaid = Convert.ToDecimal(txtAmount.Text);

    _deliveryListAmount.dtpaymentDate = Convert.ToDateTime(txtDateOfPayment.Text);

    _deliveryListAmount.decTotalAmount = Convert.ToDecimal(txtTotalAmountPaid.Text);

    if (_collectBankBll.UpdateRefNumber(_deliveryListAmount) != 0)

    {

    GetCashCollection(_deliveryListAmount);

    }

    }

    }

    and also my DAL Connection

    public abstract class DALConnection

    {

    internal string GetAppConnectionString()

    {

    return ConfigurationManager.ConnectionStrings[ConfigurationSettings.AppSettings["DefaultConnection"]].ConnectionString;

    }

    internal string GetAppConnectionString(string ConnectionName)

    {

    return ConfigurationManager.ConnectionStrings[ConnectionName].ConnectionString;

    }

    internal int GetConnectionTimeout()

    {

    string strConnectionTimeout = Convert.ToString(ConfigurationSettings.AppSettings["ConnectionTimeout"]);

    if (strConnectionTimeout.Trim() == string.Empty)

    return 30;

    else

    return int.Parse(strConnectionTimeout);

    }

    }

    the above is not T-SQL code 😉

    Without having code for spQuery_GetSequenceNo, how do you know how to use it?

    Do you have any documentation of it? If not, it's wasting of time, you may not guess how the required value is returned.

    by the way, if you want to get result from stored proc RETURN, you should use another command objects parameter with direction set to ParameterDirection.ReturnValue.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • another option iam thinking right now is to create a udf that will call this executable sp. and the result will be call to my sp that will update the txtReferrence..

  • cyberdaemon (5/13/2013)


    another option iam thinking right now is to create a udf that will call this executable sp. and the result will be call to my sp that will update the txtReferrence..

    you cannot call sp from udf

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • @ssCrazy

    You're right.. Do you any idea about this prob?

  • cyberdaemon (5/14/2013)


    @ssCrazy

    You're right.. Do you any idea about this prob?

    I'll reiterate what Eugene is saying: you need to know how to collect the value you want from the stored procedure spQuery_GetSequenceNo. Is it from an output variable or from a result set? It shouldn't be from the return value because that's reserved for a specific purpose, and your test indicates that it isn't.

    I'd recommend that you run tests using SSMS rather than your client API. It shouldn't be too hard to figure it out. Using your client introduces a degree of obfuscation.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Good day,

    Luckily i run a query just to view the DDL for the GenerateSequenceNo

    here is the code...

    ALTER PROCEDURE [dbo].[spQuery_GetSequenceNo1]

    ---spQuery_GetSequenceNo '502147940', '171156'

    @intCustomerNo VARCHAR(9) = NULL

    ,@ParamAmount VARCHAR(50) = NULL

    ,@PaymentDate DATETIME

    --------------------------

    /*

    ##_BEGIN

    CREATED BY: JANE DIAZ

    ##_END

    */

    --------------------------

    AS

    CREATE TABLE #tempSeqA

    (

    intCol INT

    ,intCustNo INT

    ,intSeqNo INT

    ,dblProduct FLOAT

    )

    DECLARE

    @counter INT

    ,@charCustno INT

    ,@charSeqno INT

    ,@SumofProduct INT

    ,@Modulo INT

    ,@Seq2 varchar(10)

    SET @counter = 0

    /*

    GET PRODUCT OF SEQUENCE NO AND CUSTOMER NO

    */

    WHILE @counter < 9

    BEGIN

    SET @counter = @counter + 1

    SET @charCustno = (SELECT SUBSTRING(CONVERT(VARCHAR,@intCustomerNo),@counter,1))

    SET @charSeqno = (SELECT TOP 1 intValue FROM tblSequenceA WHERE intCode = @counter)

    INSERT INTO #tempSeqA

    VALUES(

    @counter

    ,@charCustno

    ,@charSeqno

    ,@charCustno * @charSeqno

    )

    END

    --SELECT * FROM #tempSeqA

    /*

    GET SUM OF THE PRODUCTS

    */

    SELECT @SumofProduct = SUM(dblProduct)

    FROM #tempSeqA

    --SELECT @SumofProduct

    /*

    COMPUTE THE MODULO

    -- if remainder is 10 or 0 then value is "0" , then multiply to "9" and get the

    last digit. (last digit is the CHECK DIGIT)

    */

    SELECT @Modulo = (CASE WHEN (@SumofProduct % 11) = 10 THEN 0

    ELSE CASE WHEN (@SumofProduct % 11) = 0 THEN 0

    ELSE (@SumofProduct % 11) END

    END ) * 9

    --Declare @tablevar table(colValue varchar(10))

    Declare @tablevar table(colValue varchar(11)) --Add 1 char for check digit C of MDDYC99999; herbert k. ; Aug. 2, 2012

    insert into @tablevar

    EXEC [spQuery_GetSequenceNo2]

    @intParamAmount = @ParamAmount

    ,@dtePaymentDate = @PaymentDate

    SELECT SEQ_NO = SUBSTRING(CONVERT(VARCHAR,@Modulo),LEN(@Modulo),1) + (select top 1 * from @tablevar)

    DROP TABLE #tempSeqA

    ------------------------------------------------------------------------------------------

  • You stored proc returns SEQNO in the recordset.

    Not very well written proc I would say, crap actually.

    To get the above value from it, you should execute this proc and use SQLDataReader to read the value from the first returned row.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Good day,

    You give me a light in my darkest side of coding. you save my for this situation. I wish i could return the favor to you..

    i'll try you're suggestion and i hope that i will execute very well.

    once again thank you Eugene Elutin

  • I'm glad to help!

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply