Update In Trigger Not Working
Sep 24, 2007
Hi,
I am trying to concatenate the columns (PrevEmp01, PrevEmp02, PrevEmp03, PrevEmp04, PrevEmp05) into column (ft) using trigger:
CREATE TRIGGER [tg_prevemp_ft_update] ON [tStaffDir_PrevEmp]
FOR INSERT, UPDATE
AS
UPDATE tStaffDir_PrevEmp SET ft = PrevEmp01 + ' ' + PrevEmp02 + ' ' + PrevEmp03 + ' ' + PrevEmp04 + ' ' + PrevEmp05
I would expect the (ft) column will be populated accordingly regardless if any of the columns are (Null).
But the Trigger will only work when all the 5 columns are populated. If one of the column is (Null), the (ft) column will be (Null) too.
Please advise. Many Thanks.
View 2 Replies
ADVERTISEMENT
Oct 4, 2004
We've altered an UPDATE trigger - now it doesn't work properly. When updating a record (via a web application) by clearing the value for a particlar column, we get error:
UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
Code = 800a0c93
Source = ADODB.Recordset
Description = Operation is not allowed in this context.
What the trigger does is prevent a zero-length string from being inserted into the database, which violates a check constraint, and instead inserts NULL. The web app - whose code we cannot modify - apparently does this. This was working fine, until we altered the trigger to add an additional column for update. Our SQL developer person has left, so us less knowledgable (about SQL) folks are trying to pick up the slack.
trigger code:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER TRIGGER trigViaNetCardholderUpdateEmptyStrings
ON viewViaNetCardholder
INSTEAD OF UPDATE AS
UPDATE tblIDCross
SET
tblIDCross.chUSCId = CASE WHEN ins.chUSCId = '' THEN NULL ELSE ins.chUSCId END,
tblIDCross.chNineDigit = CASE WHEN ins.chNineDigit = '' THEN NULL ELSE ins.chNineDigit END,
tblIDCross.chPID = CASE WHEN ins.chPID = '' THEN NULL ELSE ins.chPID END,
tblIDCross.chEmployeeId = CASE WHEN ins.chEmployeeId = '' THEN NULL ELSE ins.chEmployeeId END,
tblIDCross.chAIMSNumber = CASE WHEN ins.chAIMSNumber = '' THEN NULL ELSE ins.chAIMSNumber END,
tblIDCross.intCustomerType = ins.intCustomerType
FROM INSERTED ins
WHERE tblIDCross.intUSCardId = ins.intUSCardId
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Please help...
View 1 Replies
View Related
Oct 20, 2006
The Folowing code is not working anymore. (500 error)
Set objRS = strSQL1.Execute
strSQL1 = "SELECT * FROM BannerRotor where BannerID=" & cstr(BannerID)
objRS.Open strSQL1, objConn , 2 , 3 , adCmdText
If not (objRS.BOF and objRS.EOF) Then
objRS.Fields("Exposures").Value =objRS.Fields("Exposures").Value + 1
objRS.update
End If
objRS.Close
The .execute Method works fine
strSQL1 = "UPDATE BannerRotor SET Exposures=Exposures+1 WHERE BannerID=" & cstr(BannerID)
objConn.Execute strSQL1
W2003 + IIS6.0
Pls advice?
View 1 Replies
View Related
Jul 20, 2005
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
View 1 Replies
View Related
Jan 7, 2004
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)
Try
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = "done"
cn.Close()
Catch ex As Exception
Label1.Text = ex.Message
End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
Please I would appreciate any advice...
View 2 Replies
View Related
Aug 6, 2000
Hi,
Am having problems creating trigger. I want to have a trigger update a
table (tblClaimItemID) with the value of ClaimItemID + 1 and insert this
value as the primary key of tblClaimItemsDaily(to field ClaimItemID) on
insert of a record into tblClaimItemsDaily (so that when data is appended to
tblClaimsItems we have a unique primary key). tblClaimItemsDaily is the day
sheet, so need to have another table generate the primary key since there
will be multiple tblClaimItemsDaily tables open at any given time, but the
key must be unique. I've been looking at deja and in my books, but they
don't seem to indicate how to do this.
Here's the code. Any ideas?
------------begin code-------
CREATE TRIGGER trgClaimItemID
ON [tblClaimItemsDaily]
FOR INSERT,UPDATE
AS
DECLARE @Item int
IF UPDATE(ClaimID)
BEGIN
IF @@ROWCOUNT = 1
Update dbo.tblClaimItemID
SET dbo.tblClaimItemID.ClaimItemID = dbo.tblClaimItemID.ClaimItemID +
1
FROM dbo.tblClaimItemID
Select @Item = dbo.tblClaimItemID.ClaimItemID FROM dbo.tblClaimItemID
END
UPdate tblClaimItemID set ClaimItemID = @Item
------------end code-------
View 1 Replies
View Related
Nov 3, 2004
i am having an issue with my trigger code
i believe that i should be able to use the multiple if else statements
and i just threw in an update in each begin end statement
but when i check the syntax it fails
any reason why that is
CREATE TRIGGER [tr_update_conveyor_interface] ON [dbo].[t_conveyor_interface]
FOR UPDATE
AS
declare
@id integer,
@barcode varchar(25),
@epc_tag varchar(24),
@reject_flag char(1),
@hold_flag char(1),
@pe_3_flag char(1),
@old_id integer,
@old_barcode varchar(25),
@old_epc_tag varchar(24),
@old_reject_flag char(1),
@old_hold_flag char(1),
@old_pe_3_flag char(1)
select @barcode = c.barcode,
@id = c.id,
@epc_tag = c.epc_tag,
@reject_flag = c.reject_flag,
@hold_flag = c.hold_flag,
@pe_3_flag = c.pe_3_flag
from t_conveyor_interface c
inner join deleted d
on c.barcode = d.barcode
select @old_barcode = c.barcode,
@old_id = c.id,
@old_epc_tag = c.epc_tag,
@old_reject_flag = c.reject_flag,
@old_hold_flag = c.hold_flag,
@old_pe_3_flag = c.pe_3_flag
from t_conveyor_interface c
inner join inserted i
on c.barcode = i.barcode
if(@old_epc_tag <> @epc_tag)
begin
update t_load_audit
set id = 1
end
else
if(@old_reject_flag <> @reject_flag)
begin
update t_load_audit
set id = 1
end
else
if(@old_hold_flag <> @hold_flag)
begin
update t_load_audit
set id = 1
end
else
if(@old_pe_3_flag <> @pe_3_flag
begin
update t_load_audit
set id = 1
end
View 1 Replies
View Related
Dec 16, 2004
Hi,
here is my code for a trigger,
--------------------------------
CREATE TRIGGER trIns_Test
ON tblTest
FOR INSERT
AS
DECLARE @Message VARCHAR(100),
@Num INT
SELECT @Num = user_id from INSERTED
SELECT @Message = 'NET SEND NICK '+' Here is the new # - ' + CONVERT(VARCHAR(100), @Num)
EXEC master.dbo.xp_cmdshell @Message
--------------------------------
But when i insert a row into tblTest table i'am supposed to receive a NET SEND message. But its not happening if i hardcode the value @Num i.e
SELECT @Num = 321
Then i receive a message, but why this INSERTED clause does't work i'm unable to trace out.
Version of SQL is 2K SP3
Thanks,
View 2 Replies
View Related
May 4, 2004
Hi,
I have this trigger, it is working fine when i add new data but it doesn't work when I delete data from the table?
Any idea?
Any help will be highly appreciated.
CREATE TRIGGER [PROP_AMT] ON [dbo].[cqe_item]
FOR INSERT, UPDATE, DELETE
AS
DECLARE
@var_DB_contract INTEGER,
@var_CQE INTEGER,
@var_PC INTEGER,
@var_item VARCHAR(7),
@var_AMT_PAID INTEGER,
@var_AMT_RET INTEGER,
@var_ITEM_NEW VARCHAR(1),
@var_quant DECIMAL,
@var_fiyr INTEGER,
@var_amt_result INTEGER,
@var_amt_ret_result INTEGER,
@var_amt_old INTEGER,
@var_amt_ret_old INTEGER,
@var_quant_result INTEGER,
@var_quant_new INTEGER,
@var_quant_old INTEGER,
@Item_new VARCHAR(7),
@var_chk varchar(1)
--If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
set @var_db_contract =(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
IF @var_db_contract IS NOT NULL
BEGIN
SET @var_db_contract=(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_cqe=(SELECT a.cqe_numb FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_pc=(SELECT a.pc_code FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_item=(SELECT a.item_no FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_fiyr=(SELECT a.fy_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
set @var_chk ="Y"
END
ELSE
BEGIN
SET @var_db_contract=(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_cqe=(SELECT a.cqe_numb FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_pc=(SELECT a.pc_code FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_item=(SELECT a.item_no FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_fiyr=(SELECT b.fy_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
set @var_chk="N"
END
SET @var_amt_paid=(SELECT a.amt_paid_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_amt_old=(SELECT b.amt_paid_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_amt_result =ISNULL(@var_amt_paid,0) - ISNULL(@var_amt_old,0)
SET @var_amt_ret = (SELECT a.amt_ret_item from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @var_amt_ret_old=(SELECT b.amt_ret_item from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @var_amt_ret_result = isnull(@var_amt_ret,0) - isnull(@var_amt_ret_old,0)
SET @var_quant_new = (SELECT a.quantity from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @var_quant_old =(SELECT b.quantity from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @var_quant_result = isnull(@var_quant_new,0) - isnull(@var_quant_old,0)
SELECT @item_new = new_item
FROM VALID_ITEM
WHERE DB_CONTRACT = @var_db_contract
AND PC_CODE = @var_PC
AND ITEM_NO = @var_ITEM
UPDATE ae_contract
set amt_paid_contr = isnull(amt_paid_contr,0) +@var_amt_result,
amt_ret_contr = isnull(amt_ret_contr,0) + @var_amt_ret_result
where db_contract = @var_db_contract
IF @item_new = 'N'
BEGIN
update vendor
set used_amt = isnull(used_amt,0) + @var_amt_result + @var_amt_ret_result
where db_vendor = (select gen_contr from ae_contract
where ae_contract.db_contract=@var_db_contract);
END
UPDATE enc_det
set amt_paid_fy = isnull(amt_paid_fy,0) + @var_amt_result,
amt_ret_fy = isnull(amt_ret_fy,0) + @var_amt_ret_result
where db_contract = @var_db_contract
and pc_code = @var_pc
and fy = @var_fiyr
UPDATE valid_item
set tamt_ret_item = isnull(tamt_ret_item,0) + @var_amt_ret_result,
tamt_paid_item = isnull(tamt_paid_item,0) + @var_amt_result,
qtd = isnull(qtd,0) + @var_quant_result
where db_contract = @var_db_contract
and pc_code = @var_pc
and item_no = @var_item
View 1 Replies
View Related
Jan 16, 2008
Hi Experts,
The trigger is not sucessfully calling the Stored Procedure. This was working up until last year (12/21/2007 @ 1600 hrs.)Here I am providing codes for both Trigger and Storedprocedure.Can you help me out where the problem is?
Trigger
USE [personnelreq]
GO
/****** Object: Trigger [tgrTracking] Script Date: 01/16/2008 11:55:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/*****************************************************************************************************************************************************************
* Trigger Name: tgrTracking
* Description: Calls the CalcTrackingHours stored procedure for replacement and net staff addition
* requisitions when the requisitions are emailed, transferred, or completed.
* History
* TKT/CO # Date Developer Description
* ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
* 01/03/2005 Zsofia Horompoli Created
* CO#1570 03/22/2005 Zsofia Horompoli Added originator email tracking for transfer events
*****************************************************************************************************************************************************************/
CREATE TRIGGER [tgrTracking] ON [dbo].[Log2]
FOR INSERT
AS
DECLARE @reqId int -- store requisition ID
DECLARE @eventId int -- event value inserted
DECLARE @parentId int -- store parent requisition ID
DECLARE @eventTime datetime -- store date/time of event
DECLARE @email varchar(50) -- store email address
DECLARE @logId int -- store ID assigned to the record in the Log2 table
DECLARE @location varchar(30) -- Location
DECLARE @title varchar(30) -- Job title
DECLARE @multiReq int -- > 0 = Multi NSA
DECLARE @origMail varchar(50) -- Originator's email address
DECLARE @subject varchar(200) -- Email subject line
DECLARE @from varchar(50) -- From email
DECLARE @body varchar(8000) -- Email body
DECLARE @reqType int -- Requisition type (0-replacement, 1-net staff addition, 6-Bonus)
DECLARE @sReqType varchar(30) -- Requisition type description
DECLARE @appEmail varchar(500) -- Approvers' email addresses
SELECT @from = 'compensationandbenefits@west.com'
DECLARE @inserted_rows AS CURSOR -- hold the inserted rows
SET @inserted_rows = CURSOR FOR
SELECT id,reqid, timestamp, event, stuff(notes,1,patindex('%:%',notes),'') AS email FROM inserted -- get the Log2 id,requisition id, event, timestamp, and email address from the updated/inserted rows
OPEN @inserted_rows
FETCH NEXT FROM @inserted_rows INTO @logId, @reqId, @eventTime, @eventId, @email
WHILE (@@FETCH_STATUS = 0)
BEGIN
--Is this an email/transfer/complete/deny event?
IF (@eventId = 4 OR @eventId = 6 OR @eventId = 7 OR @eventId = 0 OR @eventId = 10)
BEGIN -- Yes, is this a deny event?
IF (@eventId = 0)
BEGIN --Yes, is this an active replacement/net staff addition/parent multi NSA requisition?
SELECT @parentId = id
FROM reqs2 r
WHERE requisitiontype in (0,1) and id = @reqId and
(SELECT ParentReqId from ParentChild WHERE ChildReqId = @reqId) IS NULL and
NOT (markedfortransfer = 0 AND logging <> 1) AND markedfortransfer <> 2
END
ELSE IF (@eventId = 6)
BEGIN
SELECT @parentId = id
FROM reqs2 r
WHERE requisitiontype IN (0,1,6) AND id = @reqId AND
(SELECT ParentReqId FROM ParentChild WHERE ChildReqId = @reqId) IS NULL and
NOT (markedfortransfer = 0 AND logging <> 1) and
(SELECT id FROM Log2 WHERE reqId = @reqId AND event = 0) IS NULL
END
ELSE IF (@eventId = 7 OR @eventId = 10)
BEGIN
SELECT @parentId = id
FROM reqs2 r
WHERE requisitiontype IN (0,1) AND id = @reqId AND
(SELECT ParentReqId FROM ParentChild WHERE ChildReqId = @reqId) IS NULL and
markedfortransfer <> 2 and
(SELECT id FROM Log2 WHERE reqId = @reqId AND event = 0) IS NULL
END
ELSE
BEGIN -- No, is this an active replacement/net staff addition/parent multi NSA requisition?
SELECT @parentId = id
FROM reqs2 r
WHERE requisitiontype IN (0,1) AND id = @reqId AND
(SELECT ParentReqId FROM ParentChild WHERE ChildReqId = @reqId) IS NULL and
NOT (markedfortransfer = 0 AND logging <> 1) AND markedfortransfer <> 2 and
(SELECT id FROM Log2 WHERE reqId = @reqId AND event = 0) IS NULL
END
--Is this a requisition to be tracked?
IF @parentId is not NULL
BEGIN --Yes, is this a transfer event?
IF @eventId = 6
BEGIN --Yes, retrieve information for email
SELECT @location = RTRIM(L.DESCR), @title = RTRIM(PT.DESCR), @multiReq = (SELECT COUNT(*) FROM ParentChild WHERE ParentReqID = R.id), @reqType = R.requisitiontype
FROM reqs2 R
LEFT OUTER JOIN TDSdev.dbo.PS_LOCATION_TBL L ON R.sitename = L.LOCATION
LEFT OUTER JOIN TDSdev.dbo.PS_JOBCODE_TBL PT ON R.jobtitle = PT.JOBCODE
WHERE R.id = @reqId
-- Get email addresses to use
EXEC sel_EmailUsers @reqId = @reqId, @bApprover = 1, @currentUser = NULL, @origEmail = @origMail OUTPUT, @appEmail = @appEmail OUTPUT
--Set requisition type description
IF @reqType = 0
BEGIN --Replacement requisition
SELECT @sReqType = 'Replacement'
END
ELSE IF @reqType = 1 AND @multiReq > 0
BEGIN --Multi-NSA
SELECT @sReqType = 'Multiple Net Staff Addition'
END
ELSE IF @reqType = 1
BEGIN --NSA
SELECT @sReqType = 'Net Staff Addition'
END
ELSE IF @reqType = 6
BEGIN --Bonus
SELECT @sReqType = 'Bonus'
END
--Build subject line
--Do we have a location?
IF @location IS NULL
BEGIN --No, default to Nothing
SELECT @location = ''
END
--Do we have a job title?
IF @title IS NULL
BEGIN --No, default to Nothing
SELECT @title = ''
END
SELECT @subject = @location + ': ' + @sReqType + ': ' + CAST(@reqId AS varchar(15)) + ': ' + @title
--Set email body
IF @reqType = 6 -- If Bonus Req
BEGIN
SELECT @body = '<p>Bonus requisition number ' + CAST(@reqId AS varchar(15)) + ' has been processed by Compensation.</p>'
SELECT @body = @body + '<p>If you have any questions, please contact us at <a href=''mailto: compensationandbenefits@west.com'' target=''_blank''>compensationandbenefits@west.com</a> .</p>'
END
ELSE IF @reqType IN (0,1) -- If Net Staff or Replacement Req
BEGIN
SELECT @body = '<p>Requisition number ' + CAST(@reqId AS varchar(15)) + ' has been finalized and sent to Human Resources on ' + convert(char(10),@eventTime, 101) + ' at ' + substring(convert(char(19),@eventTime, 100), 12, 8) + ' for recruiting.</p>'
SELECT @body = @body + '<p>If you have any questions, please contact us at <a href=''mailto: compensationandbenefits@west.com'' target=''_blank''>compensationandbenefits@west.com</a> .</p>'
END
--Send email
EXEC usp_SMTPMail @SenderAddress = @from, @RecipientAddress = @origMail, @Subject = @subject, @Body = @body, @Cc = @appEmail
END
-- Call the CalcTrackingHours stored procedure
IF @reqType IN (0,1)
BEGIN
EXEC CalcTrackingHours @event = @eventId, @reqId = @reqId, @emailTo = @email, @endDateTime = @eventTime, @logId = @logId
END
END
END
FETCH NEXT FROM @inserted_rows INTO @logId, @reqId, @eventTime, @eventId, @email
END
--clean up
CLOSE @inserted_rows
DEALLOCATE @inserted_rows
Stored Procedure is
USE [personnelreq]
GO
/****** Object: StoredProcedure [dbo].[CalcTrackingHours] Script Date: 01/16/2008 12:01:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/****** Object: Stored Procedure dbo.CalcTrackingHours Script Date: 4/12/2005 9:20:19 AM ******/
/*****************************************************************************************************************************************************************
* Stored Procedure Name: CalcTrackingHours
* Description: Determines the appropriate tracking level, the number of business hours the requisition
* spent at the current tracking level and inserts this information into the timeLog table
* History
* Date Developer Description
* ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
* 01/03/2005 syszxh Created
*****************************************************************************************************************************************************************/
CREATE procedure [dbo].[CalcTrackingHours]
@event int, -- 4=Emailed,6=Transferred,7=Completed
@reqId int, -- Requisition Id
@emailTo varchar(50), -- Email address requisition was forwarded to
@endDateTime datetime, -- Date/time of event
@logId int -- ID assigned to new record in the Log2 table
as
set nocount on
declare @currentUserType as int -- Current user type
declare @startDateTime as datetime -- Date/time requisition arrived at current step
declare @newUserType as int -- New user type
declare @currentTrackLevel as varchar(5) -- Tracking level to be inserted into the timeLog table
declare @newTrackLevel as varchar(5) -- New tracking level
declare @trackEmail as varchar(50) -- Email address at current tracking level
declare @count as int -- counter variable
declare @errorValue as int -- Error flag (1 = start date/time missing)
declare @trackHours as decimal(10,2)
select @errorValue = 0
select @startDateTime = NULL
-- Retrieve last email's user type, date/time stamp, and email address
select top 1 @currentUserType = userType, @startDateTime = timestamp, @trackEmail = n.userid
from personnelreq.dbo.Log2 l left outer join personnelreq.dbo.names2 n on stuff(notes,1,patindex('%:%',notes),'') = n.userid
where (l.event = 4 or l.event = 10) and timestamp <= @endDateTime and reqid = @reqId and l.id < @logId order by l.id desc
-- Last email found?
if @startDateTime is NULL
begin
-- No, this is the very first email, retrieve new user type
select @newUserType = userType
from personnelreq.dbo.names2 n
where userid = @emailTo
-- Is new user @C&B?
if @newUserType = 2
begin -- Yes, set new tracking level
select @newTrackLevel = 'cb1'
end
else
begin -- No, set new tracking level
select @newTrackLevel = 'dept'
end
end
else
begin
-- Is this event = email?
if @event = 4
begin
-- Retrieve new user type
select @newUserType = userType
from personnelreq.dbo.names2 n
where userid = @emailTo
-- Is the current user the same as the new user?
if @currentUserType = @newUserType and @emailTo = @trackEmail
begin
-- Yes, get latest level
select top 1 @newTrackLevel = trackLevel
from timeLog
where reqID = @reqId and trackEmail = @trackEmail and dateIn = @startDateTime
order by dateIn desc
select @currentTrackLevel = @newTrackLevel
end
-- Is the current user type @C&B?
else if @currentUserType <> 2 or @currentUserType is NULL
begin
-- No, retrieve current tracking level
exec GetTrackLevel @userType = @currentUserType, @reqID = @reqID, @email = @trackEmail, @trackLevel = @currentTrackLevel output
-- Is the new user type @C&B?
if @newUserType = 2
begin -- Yes, retrieve new tracking level
exec GetCBLevel @trackLevel = @currentTrackLevel, @reqID = @reqID, @currentTrackLevel = @newTrackLevel output
end
else -- No, retrieve new tracking level
begin
exec GetTrackLevel @userType = @newUserType, @reqID = @reqID, @email = @emailTo, @trackLevel = @newTrackLevel output
end
end
else
begin
-- Yes, retrieve new tracking level
exec GetTrackLevel @userType = @newUserType, @reqID = @reqID, @email = @emailTo, @trackLevel = @newTrackLevel output
-- Retrieve current tracking level
exec GetCBLevel @trackLevel = @newTrackLevel, @reqID = @reqID, @currentTrackLevel = @currentTrackLevel output
end
end
-- Is this event = transfer/completed?
else if @event = 6 or @event = 7 or @event = 10
begin
-- No, any existing entries for exec3?
if (select count(*) from timeLog where trackLevel = 'exec3' and reqID = @reqID) > 0
begin
-- Yes, set current tracking level to cb5
select @currentTrackLevel = 'cb5'
end
-- Any existing entries for exec2?
else if (select count(*) from timeLog where trackLevel = 'exec2' and reqID = @reqID) > 0
begin
-- Yes, set current tracking level to cb4
select @currentTrackLevel = 'cb4'
end
-- Any existing entries for exec1?
else if (select count(*) from timeLog where trackLevel = 'exec1' and reqID = @reqID) > 0
begin
-- Yes, set current tracking level to cb3
select @currentTrackLevel = 'cb3'
end
-- Any existing entries for acct?
else if (select count(*) from timeLog where trackLevel = 'acct' and reqID = @reqID) > 0
begin
-- Yes, set current tracking level to cb2
select @currentTrackLevel = 'cb2'
end
else
begin
-- Set current tracking level to cb1
select @currentTrackLevel = 'cb1'
end
end
-- Calculate hours worked
if @event <> 10
begin
exec CalcBusinessHours @startDateT = @startDateTime, @endDateT = @endDateTime, @totalHours = @trackHours output
-- Update information in the timeLog table
update timeLog
set dateOut = @endDateTime, trackHours = @trackHours
where reqID = @reqID and trackHours = 0 and dateOut = '1990-12-31'
end
end
-- Is this event = email?
if @event = 4 and @newTrackLevel is not NULL
begin
-- Yes, insert new row for new track step
insert into timeLog (reqID, trackLevel, dateIn, trackEmail, trackHours, dateOut)
values (@reqID, @newTrackLevel, @endDateTime, @emailTo, 0, '1990-12-31')
end
-- Is this event = reopen?
else if @event = 10 and @trackEmail is not NULL
begin -- Yes, insert new row for new track step
insert into timeLog (reqID, trackLevel, dateIn, trackEmail, trackHours, dateOut)
values (@reqID, @currentTrackLevel, @endDateTime, @trackEmail, 0, '1990-12-31')
end
endProcedure:
Return @errorValue
View 2 Replies
View Related
Jan 3, 2005
hi!
I have a big problem. If anyone can help.
I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.
I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.
But i don't know exactly how to do the coding for this?
Is there any other way to do this?
can DBCC help to retrieve this info?
Please advise me how to do this.
Thanks in advance.
Vaibhav
View 10 Replies
View Related
Jul 8, 2015
I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
Â
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),
I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.
I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?
View 7 Replies
View Related
May 9, 2007
i have this trigger in my database : ALTER TRIGGER dbo.AddVoucher ON dbo.User_AddVoucher AFTER INSERTAS SET NOCOUNT ON;DECLARE @UserId int, @Add_id int, @voucher_id char, @Kredit money, @date smalldatetime, @last_balance money, @voucher_status charSELECT @UserId = UserId, @voucher_id = Voucher_ID, @Add_id = Add_id, @date = Deposit_Date FROM InsertedSELECT @Kredit= Voucher_Value, @voucher_status = Voucher_Status FROM Voucher WHERE Voucher_ID = @voucher_idINSERT INTO User_Balance(AddVoucher_ID, UserId, Update_Type, Update_Date) VALUES (@Add_id,@UserId, 'Kredit',@date)select @last_balance = Balance from User_Balance WHERE UserId = @UserId and Balance = (select TOP 1 Balance User_Balance where UserId = @UserId order by Update_Id DESC) if (@voucher_status = 'active') -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. -- Insert statements for trigger hereBEGIN update User_Balance set Balance = @last_balance + @Kredit where AddVoucher_ID = @Add_id update Voucher set Sold_Date = @date where Voucher_ID = @voucher_idENDELSE BEGIN raiserror ('Voucher is not valid',0,1) rollback transaction ENDgo the problem is the update function is not working and the if statement always put to 'FALSE' do you think anything wrong with the code
View 2 Replies
View Related
May 30, 2008
Hi,
I am not sure if this is the right forum to post this question.
I run an update statement like "Update mytable set status='S' " on the SQL 2005 management Studio.
When I run "select * from mytable" for a few seconds all status = "S". After a few seconds all status turn to "H".
This is a behaviour when you have an update trigger for the table. But I don't see any triggers under this table.
What else would cause the database automatically change my update?
Could there be any other place I should look for an update trigger on this table?
Thanks,
View 3 Replies
View Related
Feb 15, 2008
Hello
I've to write an trigger for the following action
When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz
all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated
statut_tiers to 1
and date_cloture to the same date as entered
the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture
thank you for your help
I've never done a trigger before
View 14 Replies
View Related
Mar 21, 2008
Hi SQL Proz,
I am trying to important some data in the an existing table with trigger. Unfortunately, the data were successfully inserted into the said table but the trigger did not work out. Why? How to do the tricks?
Many Thanks!
View 2 Replies
View Related
Apr 20, 2015
In My Table , I have Three Column like Qty,Price and Total, I create a Trigger for this .Â
Table:
CREATE TABLE [dbo].[tbl_SO](
[SoNo] [varchar](50) NULL,
[Qty] [int] NULL,
[price] [numeric](18, 2) NULL,
[total] [numeric](18, 2) NULL
) ON [PRIMARY]
[code]...
Input :
1) UPDATE TBL_SO SET QTY='10',Price='100' Where SONo='10'
2)UPDATE TBL_SO SET QTY='10',Price='100' Â Total=Qty*Price Where SONo='10'
Output:
What I get? I tried both 1 and 2, It(Query) does not work on first time,I executed (Query) second time It works.Why?What I need?  How to work on First Time?
View 6 Replies
View Related
Sep 14, 2007
Hello all,
I have I trigger where I want to insert all _changed_ rows from the INSERTED table into
a table called tempProducts.
If I put this query inside my trigger, I selects exactly the rows I want: rows changed
Code SnippetSELECT * FROM INSERTED
EXCEPT SELECT * FROM DELETED
I the current trigger I have
Code SnippetINSERT INTO dbo.TempProducts (LBTyp, CountryOfOrigin)
SELECT LBTyp, CountryOfOrigin
FROM INSERTED
but this inserts ALL updated rows, not only the changed ones.
So I thought hey, I´ll just combine the two querys and the problem will be solved, like so:
Code Snippet
INSERT INTO dbo.TempProducts (LBTyp, CountryOfOrigin)
SELECT LBTyp, CountryOfOrigin
FROM (SELECT * FROM INSERTED
EXCEPT SELECT * FROM DELETED) as Temp
But for some reason, this won´t work! Why is this? What am I doing wrong?
View 10 Replies
View Related
Dec 17, 2001
I have an update trigger which fires from a transactiion table to update a parent record in another table. I am getting no errors, but also no update. Any help appreciated (see script below)
create trigger tr_cmsUpdt_meds on dbo.medisp for UPDATE as
if update(pstat)
begin
update med
set REC_FLAG = 2
from deleted dt
where med.uniq_id = dt.uniq_id
and dt.pstat = 2
and dt.spec_flag = 'kop'
end
View 1 Replies
View Related
May 30, 2008
I am trying to update a fields with an UPDATE statement but I keep getting the error message when I run the query.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I have this Update trigger that I know is causing the error message because I guess it's not built to manage multi-row updates.
Can someone help me re-write it. I also tried using the WHERE p.ID = p.ID but when I do that it modifies all rows in the modifieddate column instead of just the cells/rows that I'm updating
ALTER TRIGGER [dbo].[MultitrigCA]
ON [dbo].[ProdDesc]
AFTER UPDATE
AS
SET NOCOUNT ON
IF UPDATE (codeabbreviation)
UPDATE p
sET p.ModifiedDate = GETDATE()
FROM ProdDesc AS p
WHERE p.ID = (SELECT ID FROM inserted)
View 7 Replies
View Related
Jul 20, 2005
Hi there,I'm a little stuck and would like some helpI need to create an update trigger which will run an update query onanother table.However, What I need to do is update the other table with the changedrecord value from the table which has the trigger.Can someone please show me how this is done please??I can write both queries, but am unsure as to how to get the value ofthe changed record for use in my trigger???Please helpM3ckon*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Apr 3, 2008
hi!
i have a database with about 20 tables. i appended to each table a column "UpdatedOn", and i want to write a trigger to set the date of the update date in that column, using a trigger.
i want to avoid the trigger launching for the last column (UpdatedOn).
how can i detect the rows that changed, and modify only the update date/time?
i read something about TableName_Inserted and TableName_Deleted, but i would prefer to copy as generic as possible the data from there, meaning, not to write column names in my script.
another idea i thought about was to prevent the trigger executing if no other column except for UpdatedOn changed, but... i encounter some trouble, when i try to pass column name (as string) to UPDATE() function.(Error: Expecting ID or QUOTED_ID)
thank you in advance.
View 8 Replies
View Related
Jan 27, 2008
Hello Just having an issue with my code not updating my tables. Fairly new to asp so its frustrating me that its not working Here is my code 1 Protected Sub btnAddBusDetails_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAddBusDetails.Click
2 Dim datasource As New SqlDataSource()
3 datasource.ConnectionString = ConfigurationManager.ConnectionStrings("insolvency_dbConnectionString").ToString()
4
5 datasource.UpdateCommand = "Update PrincipalContact set [princName] = @Name, [princPosition] = 'The man', [princContactNumber] = 'At home' Where ([username] = @userName);"
6 datasource.UpdateCommandType
7 datasource.UpdateParameters.Add("userName", My.User.Name)
8 datasource.UpdateParameters.Add("Name", Principal_Name.Text)
9 datasource.UpdateParameters.Add("Position", Principal_Position.Text)
10 datasource.UpdateParameters.Add("Contact", Principal_Contact.Text)
11 datasource.Update()
12
13
14 Dim datasource2 As New SqlDataSource()
15 datasource2.ConnectionString = ConfigurationManager.ConnectionStrings("insolvency_dbConnectionString").ToString()
16
17 datasource2.UpdateCommand = "Update Firm set [insolvencyPractitioner] = @insolvencyPractitioner, [companyName] = @companyName, [tradingAs] = @tradingAs, [businessDescription] = @businessDescription, [principalAddress] = @principalAddress, [tradingStatus] = @tradingStatus, [numberOfEmployees] = @numberOfEmployees, [locationsByState] = @locationsByState Where ([userName] = @userName);"
18 datasource2.UpdateParameters.Add("userName", My.User.Name)
19 datasource2.UpdateParameters.Add("insolvencyPractitioner", Insolvency_Practitioner.Text)
20 datasource2.UpdateParameters.Add("companyName", Company_Name.Text)
21 datasource2.UpdateParameters.Add("tradingAs", Trading_As.Text)
22 datasource2.UpdateParameters.Add("businessDescription", Description_of_Business.Text)
23 datasource2.UpdateParameters.Add("principalAddress", Principal_Address.Text)
24 datasource2.UpdateParameters.Add("tradingStatus", Trading_status.Text)
25 datasource2.UpdateParameters.Add("numberOfEmployees", Number_of_Employees.Text)
26 datasource2.UpdateParameters.Add("locationsByState", Location_by_state.Text)
27
28 datasource2.Update()
29
30 Response.Redirect("~/Default.aspx")
31
32
33
34 End Sub No errors are occurring and when I replace the parameters with actual text it works.eg. Update Firm set [insolvencyPractitioner] = @insolvencyPractitioner, [companyName] = @companyName .... to Update Firm set [insolvencyPractitioner] = 'Luke', [companyName] = 'A Company' ..... Not sure why this is happening. Can someone please give me a hand. Thanks
View 5 Replies
View Related
Mar 19, 2000
I have a table and I want to update it with data that is available in a
different table, the Master table contains a field called RegID and the other table also has RegId - I am bombing out with this UPDATE QUERY :
UPDATE trainee
SET tra_HomePhone = phone$.EPhoneH,
tra_areaHomePhone = phone$.EPhoneHA,
tra_workPhone = phone$.EPhoneW,
tra_areaWorkPhone = phone$.EPhoneWA,
tra_postcode = phone$.EAddressHP
WHERE trainee.tra_regId = phone$.regID
Any reason why this is not working? Thanks in advance for any help.
Anthony
View 2 Replies
View Related
Sep 6, 2006
I'm new to ASP and ASP.NET so I used the Wizards in Visual Web Deverlopment Express 2005 to build the following code:<asp:DetailsView ID="DetailsView" runat="server" DataSourceID="TracksDataSource"
Height="50px" Width="125px" AutoGenerateEditButton="True" AutoGenerateRows="False">
<Fields>
<asp:BoundField DataField="pk_trackID" HeaderText="pk_trackID" ReadOnly="True" SortExpression="pk_trackID" />
<asp:BoundField DataField="trackName" HeaderText="trackName" SortExpression="trackName" />
<asp:BoundField DataField="trackPath" HeaderText="trackPath" SortExpression="trackPath" />
<asp:BoundField DataField="lyrics" HeaderText="lyrics" SortExpression="lyrics" />
</Fields>
</asp:DetailsView>
<asp:SqlDataSource ID="TracksDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:connectionString %>"
SelectCommand="SELECT * FROM [Tracks] WHERE ([pk_trackID] = @pk_trackID)" UpdateCommand="UPDATE [Tracks] SET [trackName] = @trackName, [trackPath] = @trackPath, [lyrics] = @lyrics WHERE [pk_trackID] = @pk_trackID" >
<UpdateParameters>
<asp:Parameter Name="trackName" Type="String" />
<asp:Parameter Name="trackPath" Type="String" />
<asp:Parameter Name="lyrics" Type="String" />
<asp:Parameter Name="pk_trackID" Type="String" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="TracksListBox" Name="pk_trackID" PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource> However, when I click Edit, change something, and then Update it doesn't update the database. However, if I remove the DataField bindings and use the AutoGenerateRows feature it works fine.
View 7 Replies
View Related
Sep 26, 2006
I am trying to update a field in gridview. My update is not working, sort of. The original value is being updated with a NULL value when I click on the update button.here is my code <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource2" AutoGenerateEditButton="True" DataKeyNames="rqt_id">UpdateCommand="UPDATE [rqt_data] SET [rqt_title]=@rqt_title WHERE [rqt_id] = @rqt_id" > <asp:Parameter Name="rqt_title" Type="String" /> the field rqt_title is set up as a NVarChar(25) in the db. I can't specify that for the Type property. Could that be the issue?thx
View 4 Replies
View Related
Feb 13, 2007
i am using visual web developer 2005 and SQL Express 2005 and VB as the code behindi have a table called orderdetail and i want to update the fromdesignstatus field from 0 to 1 in one of the rows containing order_id = 2so i am using the following coding in button click event Protected Sub updatebutton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim update As New SqlDataSource() update.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() update.UpdateCommandType = SqlDataSourceCommandType.Text update.UpdateCommand = "UPDATE orderdetail SET fromdesignstatus = '1' WHERE order_id = '2'" End Sub but the field is not updatedi do not know where i have gone wrong in my coding. i am sure that my database connection string is correctplease help me
View 1 Replies
View Related
Sep 24, 2007
Hi,I have three tables
Time_Sheet
Pin_Code
P_Date
Day_Status
Primary Key
Primary Key
Leave
P_Number
Leave_Code
Start_Date
End_Date
Primary Key
Employees
P_Number
Pin_Code
More>>
Primary Key
Primary Key
I want to update Day_Status in Time_Sheet from Leave_Code (Leave) when P_Date in Time_Sheet between start date and End Date in Leave I am getting Msg 512, Level 16, State 1, Line 1Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated.Please help me.Thanks,Janaka
View 2 Replies
View Related
Jan 29, 2008
Can someone please tell me why in the bloody hell this isnt working? It ignores the WHERE VENDORID match portion and marks all instances of USERID match to TRUE. I've been banging my head for an hour... have I really forgotten basic sql???!!!!public static void UpdateVendor(VendorEvaluationEntity VEE)
{int vendorid = Convert.ToInt32(VEE.VendorevalVendor);
int userid = Convert.ToInt32(VEE.VendorevalUser);SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["VendorEvaluationConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand("Update tblVendorUser set vendoruser_vendor_evaluated = 'true' where (vendoruser_vendor_id = @vendorid) and (vendoruser_user_id=@userid)", conn);SqlParameter pmvendorid = new SqlParameter();
SqlParameter pmuserid = new SqlParameter();pmvendorid.ParameterName = "@vendorid";pmvendorid.SqlDbType = SqlDbType.Int;
pmvendorid.Value = vendorid;
pmuserid.ParameterName = "@userid";pmuserid.SqlDbType = SqlDbType.Int;
pmuserid.Value = userid;
cmd.Parameters.Add(pmvendorid);
cmd.Parameters.Add(pmuserid);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
View 3 Replies
View Related
Feb 18, 2008
hi, i am tryuing to use the gridview as means for the user to be able to edit delete and update columns of the database. however when it is run in the browser it allows the user to edit the fields but when i click on the update button it throws an error. can someone please offer me advice on how i can sort this problem out or provide me with any examples as i cant see what the error is. i used the option in edit columns which allows you to specify you want update delete etc controls added to the gridview. how can i make it so it supports updating? thank you
Updating is not supported by data source 'SqlDataSource1' unless UpdateCommand is specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.NotSupportedException: Updating is not supported by data source 'SqlDataSource1' unless UpdateCommand is specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
View 2 Replies
View Related
Jun 9, 2008
Hi,
Can anyone tell me why my Update attempts are not working? Here is my code:
<body>
<form id="form1" runat="server">
<div>
<asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="125px"
AutoGenerateEditButton="True" AutoGenerateRows="False" DataSourceID="SqlDataSource1"
DefaultMode="Edit">
<Fields>
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
</Fields>
</asp:DetailsView><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringListings %>"
SelectCommand="SELECT [City] FROM [Listings]"
UpdateCommand="UPDATE [Listings] Set [City]=@City WHERE [ListingID]=@ListingGuid ">
<UpdateParameters>
<asp:Parameter Name="City" />
<asp:Parameter Name ="ListingGuid" />
</UpdateParameters>
</asp:SqlDataSource>
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
<asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>
</div></form>
</body>
And here is my code behindProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load ListingGuid = Request.QueryString("GUID")
End SubProtected Sub DetailsView1_ItemUpdated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdatedEventArgs) Handles DetailsView1.ItemUpdated
Label1.Text = "updated"
Label2.Text = ListingGuidEnd Sub
Please let me know what I am doing wrong?
View 3 Replies
View Related
Jan 5, 2006
I may have posted this in the wrong forum before, but i am trying to update a table in SQL2000 from asp.net2.0 gridview. I follow all the recommended ways and it doesnt update the row. Please help
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" DataKeyNames="ID" CellPadding="4" ForeColor="#333333" GridLines="None">
.......misc code.........
UpdateCommand="UPDATE vacationrequest SET hrapprvl = @hrapprvl, notes = @notes WHERE ([ID] = @original_ID)">
<SelectParameters>
<asp:ControlParameter ControlID="HiddenField1" Name="StartDate" PropertyName="Value"
Type="DateTime" />
<asp:Parameter DefaultValue="false" Name="hrapprvl" Type="Boolean" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Type=Boolean Name="hrapprvl" />
<asp:Parameter type=String Name="notes" />
<asp:Parameter Name="original_ID"/>
View 1 Replies
View Related
Mar 21, 2000
Hi,
I am trying to write an update query to update rows in one table.
Structure of Table1:
SocialSecurityNumber Varchar(9) -- Primary Key
Name Varchar(30)
Structure of Table2 :
SocialSecurityNumber Varchar(9)
Name Varchar(30)
Table 1 contains:
Row1: "123456789" Sally
Row2: "999999999" Bill
Row3: "333333333" Alex
Table 2 contains:
Row1: "123456789" <NULL>
Row2: "123456789" <NULL>
Basically I want to update the name column in Table 2 (based on the SocialSecurityNumber column) so that after
the update Table 2 will contain:
Row1: "123456789" Sally
Row2: "123456789" Sally
------------------------------------------------------------
First I tried:
UPDATE Table2
INNER JOIN Table1 ON Table2.SocialSecurityNumber = Table1.SocialSecurityNumber
SET Table2.Name = Table1.Name
WHERE Table2.SocialSecurityNumber = Table1.SocialSecurityNumber
This works in Access but not it SQL Server 7.0.
------------------------------------------------------------
Then I tried:
UPDATE Table2
INNER JOIN Table1 ON Table2.SocialSecurityNumber = Table1.SocialSecurityNumber
SET Table2.Name = Table1.Name
Again this works in Access but not it SQL Server 7.0.
------------------------------------------------------------
Finally I tried:
UPDATE Table2, Table1
SET Table2.Name = Table1.Name
WHERE Table2.SocialSecurityNumber = Table1.SocialSecurityNumber
This also did not work.
------------------------------------------------------------
Is there any way to write this update statement without cursors?
Thanks.
View 1 Replies
View Related
