.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

Save Changes not working correctly when SQL table has a trigger

Posted By:      Posted Date: September 24, 2010    Points: 0   Category :Windows Application
 

Hi all,

My WCF isn't working currently when a SQL table has a trigger on it that updates a column.

My table:

(SalesID int, SalesNumber nvarchar(10), OrderType nvarchar(3), OrderDate datetime) the trigger creates a new formatted string value for SalesNumber based on the OrderType and the SalesID, e.g GRN00001

The trigger is applied when the WCF service adds a new record. But the return values for SalesNumber is still NULL. I'm not sure why it will not update. If I restart the programme it retrieves the correct data...

public tblSales tblSales_Save(tblSales dsUpdated)
{
  db.tblSales.ApplyCurrentValues(dsUpdated);
  db.SaveChanges();
  return dsUpdated;
}

public tblSales tblSales_Save(tblSales dsUpdated)
{
  db.tblSales.ApplyCurrentValues(dsUpdated);
  db.SaveChanges();

  tblSales dsNew = tblSales_FindID(dsUpdated.SaleOrderID);
  return dsNew;
}


Neither way works...

 

 

 




View Complete Post


More Related Resource Links

Save Changes not working correctly when SQL table has a trigger

  

Hi all,

My WCF isn't working currently when a SQL table has a trigger on it that updates a column.

My table:

(SalesID int, SalesNumber nvarchar(10), OrderType nvarchar(3), OrderDate datetime) the trigger creates a new formatted string value for SalesNumber based on the OrderType and the SalesID, e.g GRN00001

The trigger is applied when the WCF service adds a new record. But the return values for SalesNumber is still NULL. I'm not sure why it will not update. If I restart the programme it retrieves the correct data...

public tblSales tblSales_Save(tblSales dsUpdated)
{
  db.tblSales.ApplyCurrentValues(dsUpdated);
  db.SaveChanges();
  return dsUpdated;
}

public tblSales tblSales_Save(tblSales dsUpdated)
{
  db.tblSales.ApplyCurrentValues(dsUpdated);
  db.SaveChanges();

  tblSales dsNew = tblSales_FindID(dsUpdated.SaleOrderID);
  return dsNew;
}


Neither way works...

 

 

 


how to fire trigger and save it to another table?

  

I want to create a trigger for insert and whenever i insert the data the trigger should fire and notify some value inserted i want to create log table to save this information,now i have created a trigger but i dont know how to map it to log table to notify the changes??i want some idea about this thanks.


how to get id value from the row inserted in a table with thr trigger.

  

I have a reports table with column report id and and report version. I created a trigger on this table to execute an application to do some processing each time a row is inserted in reports table. I need to pass the new report id execute this application from the trigger. How do I get the report id from the row that was inserted?


Thanks.


KeepTogether=false on table not working within rectangle

  

I have an rdlc report that has a table contained within a rectangle. Since I have multiple data sections within the BODY of the report, I am using rectangles to keep them separated.

Anyway, the problem is that <KeepTogether> set to false on the table is not working when the table is contained within the rectangle, causing unwanted page breaks and acting as though KeepToghther is always true. The rectangle does not have <KeepTogether>.  When the table is outside the rectangle everything works fine.

Any ideas?

Thanks 

 


execute update timedout expires , table with trigger due to recovery of database

  

I have table A in database A  , in table A i have trigger to insert record to database B table A, each update and insert of database A -table A, triiger fire and insert record in database B table A, when i try to update some times asp.net application gives error

"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

and then error log says System.Data.SqlClient.SqlException: Database 'database B is being recovered. Waiting until recovery is finished.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean ret

Working with CodePlex. How to save password for project?

  

I want to make public one small project on CodePlex. Everybody knows that you can work with CodePlex like with TFS client and with SVN client as well. But really I can't work with it from SVN client. When I tried to add some binary file (it was dll) I got the error "Server sent unexpected return value (200 OK) in response to PUT request for .". How to solve this problem I don't know, I found thread at CodePlex's discussions Can't commit binary files using TortoiseSVN, but last message was at past year. Ok, I'm working with TFS at work, so for me it will be not a problem connect to CodePlex with TFS. But there are some distressing case, Visual Studio always ask credentials at every new connection to project. And I didn't find a checkbox "Save credentials". But I found the method. Read more...


Generic Audit Trigger CLR C#(Works when the trigger is attached to any Table)

  
This Audit Trigger is Generic (i.e. non-"Table Specific") attach it to any tabel and it should work.  Be sure and create the 'Audit' table first though. The following code write audit entries to a Table called'Audit' with columns 'ActionType'    //varchar'TableName'    //varchar'PK'    //varchar'FieldName'    //varchar'OldValue'    //varchar'NewValue'    //varchar'ChangeDateTime'    //datetime'ChangeBy'    //varchar using System;using System.Data;using System.Data.SqlClient;using Microsoft.SqlServer.Server; public partial class Triggers{//A Generic Trigger for Insert, Update and Delete Actions on any Table[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")] public static void AuditTrigger(){SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Contextstring TName; //Where we store the Altered Table's Namestring User; //Where we will store the Database UsernameDataRow iRow; //DataRow to hold the inserted valuesDataRow dRow; //DataRow to how the deleted/overwritten valuesDataRow aRow; //Audit DataRow to build our Audit entry withstring PKString; //Will temporarily store the Primary Key Column Names and Values hereusing (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection{conn.Open();//Open the Connection//Build the AuditAdapter an

My repeating table did not open/load correctly

  
In my browser-enabled form I am using a webservice to get data from one look up table in my database. The table consists of two columns (ProdId and ProdDescription). In order to allow the user to select more than one products, I bind  this data to a repeating table where the productId column converted from a check box to a checkbox instead. So every row of this repeating table have a checkbox in the first column so the user can make selection and a textbox in the second column to list the Product Description. When the user open/load the existing form where multiple selection was made to the repeating table none of the checkboxs were checked. I have no idea how to go about to fix this issue and i can use some help. So far i have not use any codes for my form so i would like to stay codeless and using rules instead. Thank you in advance.

Trigger that deletes another table's row

  
Hi, I'm new to making triggers. I have the following trigger wich should delete a row in the Activity_Log table when I delete a row in the Poll_Comments table.... I have an error in my where clause - because it don't delete the specific row.... My trigger:ALTER TRIGGER [dbo].[deletePollCommentsTrigger] ON [dbo].[Poll_Comments] FOR DELETE AS BEGIN DECLARE @intRowCount int SELECT @intRowCount = @@RowCount IF @intRowCount > 0 BEGIN DELETE Activity_Log WHERE id IN (SELECT questionId FROM deleted) AND activityDateCreated IN (SELECT commentsCreateDate FROM deleted) AND activitySectionPId = 8 END END If I change the statement to: DELETE Activity_Log WHERE id IN (SELECT questionId FROM deleted) AND activitySectionPId = 8 It correctly deletes all rows with that specific questionId and where activitySectionPId = 8... But I want also to have a where clause on, that ask for commentsCreateDate = activityDateCreated - so I only delete one row and not all rows... How can I do that? Kind regards,simsen :-) 

How to save a collection to a table?

  
hi fiends, i have three tables UserRoles, Roles and the bridging tbale of them, UsersUserRoles . Therefore A User can have many roles, in my sp how to create collection that will be passed from the client app that holds all roles of a user? Is it possible to create variables to hold collection in SQL? thanks

SSRS Table Header working differently in and out of print layout

  
Please help. This is bizarre and seems like a bug to me. Using a table in SSRS, I have a details row, with a parent group and finally a header row.  For my header row, I am referencing a particular cell in the parent group. I am doing this so that i can have the header row repeat on each page of the group. This works fine when I'm not in Print Layout mode. However, when i switch to print layout mode, or print the report itself, the data that is presented on the screen is different then what is presented when not in print layout mode. WHAT THE F*!@!?? I've been battling this for a while now and i'm at my wits end. Has anyone else experienced different results from when you are in print layout mode as opposed to being in print layout mode?

ReportViewer Print Button not working correctly with windows 7 clients

  
I need some help being pointed in the right direction to fix the following issue: - After building several reports using reportviewer and testing with windows XP clients everything seemed to be fine.  I noticed that the first time the client loaded the asp.net website and clicked the print button it asked permission to install a small file (I assume this is to allow the reportviewer to access the clients printers).   - The problem I have is on Windows 7 Clients.  They can view/export the reports with no problem, but when they click the print button it ask permission to install the small file, but a error is given during the install process and the file is not installed.   If anyone has a solution or can point me in the right direction it would be helpfull.  I am using Visual Studios 2008 on Windows XP PRO and the site is hosted on Windows Server 2003 R3   Thanks 

Incomming Email Not Working Correctly in SharePoint 2010

  
Hello everyone, We recently upgraded from SharePoint 2007 to 2010. Prior to the upgrade I had two SharePoint Document libraries setup to receive incoming emails. We had two reports scheduled in Reporting Services and each report emailed a document library once a week. After the upgrade this stopped working. The reason this stopped working was because the file names of the attachments were the same each week, however in 2007, sharepoint would append a random 4 digits to the end of the file name. Was the removed from 2010 on purpose? After discovering this didn't work, I decided to create a workflow on these lists that anytime and item is added go change the file name to include the list ID to ensure that the file names will be unique. Only to find out that items added to the list from the incomming emails do not trigger the workflow. Doing some research on this and it looks like it was turned into Mircosoft as a bug in 2007 which had a fix, but the fix is only for 2007 is doesn't say it applies to SharePoint 2010 so I don't want to install it. Anyone know if there is a fix for this in SharePoint 2010? Thanks in advance, Matt

Trigger not firing on second table.

  
Im wondering if someone can help me. I have created 3 simple tables. The 1st table has a trigger that inserts a record into the 2nd table. The second table has a trigger that inserts into the 3rd table. When I insert a row into the 1st table the 1st trigger on this table fires but the trigger on the 2nd table does not fire.  In the triggers i have two print statements which should print out text when the trigger is firing but I never see the 2nd trigger firing. Stepping through with the debugger in SQL 2008 I am also not able to F11 into the 2nd insert statement. Im left scratching my head on this and was wondering if anyone could help here. Thanks in advance. I have included a sampel script to replicate the porblem.     CREATE TABLE [dbo].[TriggerTest1](     [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,     [Value1] [int] NULL,     [Value2] [int] NULL,  CONSTRAINT [PK_TriggerTest1] PRIMARY KEY CLUSTERED (     [Id] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [dbo].[TriggerTest2](     [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,     [Value1] [int] NULL,     [Value2] [int] NULL,  CONSTRA

After insert trigger on the same table.

  
Hi! i have the following table. CREATE TABLE [dbo].[EICANALESRES]( [IdContrato] [decimal](10, 0) NOT NULL, [EiCanalId] [decimal](10, 0) NOT NULL, [EiFechServ] [datetime] NOT NULL, [EiFechMaq] [datetime] NOT NULL, [EiFechCorr] [datetime] NOT NULL, [EiValorFis] [decimal](25, 4) NOT NULL, [EiValorNum] [decimal](25, 4) NOT NULL, [EiValorCalc] [decimal](25, 4) NOT NULL, [EiEst] [smallint] NOT NULL, PRIMARY KEY CLUSTERED ( [IdContrato] ASC, [EiCanalId] ASC, [EiFechServ] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]  I want a new record that takes all the data from from the recently inserted record where idcontrato =2 and eicanalid =91. And then give this new record a new idcontrato. Without deleting the original record. i have this so far, but it erases the original record.   alter TRIGGER [dbo].[datosdemo] ON [dbo].[EICANALESRES] AFTER INSERT AS BEGIN SET NOCOUNT ON; delete from eicanalesres where idcontrato = '13' and eicanalid = '91' and eivalorfis = '0' update dbo.eicanalesres SET idcontrato = '13' FROM INSERTED WHERE eicanalesres.idcontrato = inserted.idcontrato AND eicanalesres.eicanalid = inserted.eicanalid and eicanalesres.eifechserv = inserted.eifechserv and inserted.idcontrato = '02' and inserted.eicanalid = '91'

What service is responsible for Calendar Overlay working correctly?

  
I'm having 2 issues with my SharePoint server.  User Profile Sync stuck on starting and Calendar Overlay not working. I created a calendar overlay a month ago, containing 7 other calendars and it works fine.  It currently still works if I add items to one of the 7 calendars, those items show up on the calendar with the overaly. But if I try and create a new calendar and add overlay to it - containing different calendars, nothing appears.   No calendar items from the overlay calendars that I've added. This has only started happening since my User Profile Sync stopped working - are the two related, somehow? I get no errors at all - just a blank calendar!  Frustrating!! Thanks for any help.  

xml as text will not save in table field in some cases

  
I have a stored procedure that saves 4 fields into a data table named xml.  One of the fields is called xml, and is just a text field.  Usually, my sp properly stores the data, but occassionally the xml portion of my record is not stored.  It's on a remote server, so I'm having trouble hooking up a debugger to it, but I may have identified some charactoristics of the data in the cases where the data is not saving into the field.  I think it has a lot of "......."s mixed in with the xml.  I'm wondering if SQL could be identifying this as some kind of security threat.  Any thoughts would be super appreciated. 
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend