May 9, 2013 at 9:45 pm
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
May 10, 2013 at 3:33 am
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...
May 10, 2013 at 3:34 am
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
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
May 10, 2013 at 4:02 am
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);
}
}
May 10, 2013 at 5:16 am
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.
May 13, 2013 at 9:42 pm
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..
May 14, 2013 at 2:48 am
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
May 14, 2013 at 3:01 am
@ssCrazy
You're right.. Do you any idea about this prob?
May 14, 2013 at 3:20 am
cyberdaemon (5/14/2013)
@ssCrazyYou'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.
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
May 14, 2013 at 3:33 am
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
------------------------------------------------------------------------------------------
May 14, 2013 at 3:50 am
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.
May 14, 2013 at 4:00 am
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
May 14, 2013 at 4:47 am
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply