Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Updating field Expand / Collapse
Author
Message
Posted Thursday, May 9, 2013 9:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:10 AM
Points: 6, Visits: 21
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
Post #1451404
Posted Friday, May 10, 2013 3:33 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1451487
Posted Friday, May 10, 2013 3:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:43 AM
Points: 6,890, Visits: 14,254
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
Post #1451488
Posted Friday, May 10, 2013 4:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:10 AM
Points: 6, Visits: 21
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);
}
}
Post #1451501
Posted Friday, May 10, 2013 5:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1451525
Posted Monday, May 13, 2013 9:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:10 AM
Points: 6, Visits: 21
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..
Post #1452386
Posted Tuesday, May 14, 2013 2:48 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1452435
Posted Tuesday, May 14, 2013 3:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:10 AM
Points: 6, Visits: 21
@ssCrazy

You're right.. Do you any idea about this prob?
Post #1452440
Posted Tuesday, May 14, 2013 3:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:43 AM
Points: 6,890, Visits: 14,254
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
Post #1452446
Posted Tuesday, May 14, 2013 3:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:10 AM
Points: 6, Visits: 21
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





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








Post #1452453
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse