Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Updating field


Updating field

Author
Message
cyberdaemon
cyberdaemon
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 34
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
AND bintShipment = @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
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 5478
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
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8972 Visits: 19022
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
Exploring Recursive CTEs by Example Dwain Camps
cyberdaemon
cyberdaemon
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 34
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);
}
}
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 5478
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
cyberdaemon
cyberdaemon
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 34
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..
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 5478
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
cyberdaemon
cyberdaemon
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 34
@ssCrazy

You're right.. Do you any idea about this prob?
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8972 Visits: 19022
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
Exploring Recursive CTEs by Example Dwain Camps
cyberdaemon
cyberdaemon
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 34
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





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









Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search