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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

help on triggers inserting record problem?

Posted By:      Posted Date: October 03, 2010    Points: 0   Category :ASP.Net

I have created a trigger and i want to insert a record in emp table whenever i insert i must get the confirmation that record is added i want to do this

using triggers.

USE [mine]
/****** Object:  Trigger [dbo].[insert2]    Script Date: 10/03/2010 09:06:59 ******/
ALTER trigger [dbo].[insert2]  
   on [dbo].[emp]  
    for insert  
    declare @sno int,@result varchar(50),@empid int,@name varchar(50)  
    select @empid=empid,@name=@name from emp where @empid=empid 
    insert into triglog values (@empid,@name)  

above is my code emp is my primary table and triglog is the table where i want to get the confirmation that data is aded when a new record is inserted into emp,actually i want to know when a new record is added? is there anyway to know this when i execute the code im getting error like inserting null values into sno and result but how to pull the values from emp and enter it in triglog im confused help with this??thanks.

View Complete Post

More Related Resource Links

Problem inserting a record into database

I created a table with a foreign key:
Create table Products
    Pro_ID int Primary key not null, 
    Client_ID int Foreign key (Client_ID) references Client_Details not null,
    [Prod_Name] [varchar](50) NULL,
    Prod_Description varchar(50) null,
    Purchase_Date DateTime Not null,
    Price Money null,
    Delivered int null
I need to populate that table with some data:
insert into Products (Pro_ID,Client_ID,Prod_Name,Prod_Description,Purchase_Date,Price)
values(2024,102,'Valuation Roll 2001','Property Values for 2001',Getdate(),15000)
When I run the query I get an error:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Products__Client__03317E3D". 
The conflict occurred in database "valrollclients", table "dbo.client_details", column 'client_id'.
The st

Cast error when inserting record with Linq (to Sql)



I'm getting  the following error message: 

Unable to cast object of type 'System.Data.Linq.DataQuery`1[Username]' to type 'Username'.

I've been searching but can't find a solution.  This particular table has 4 columns. The first column is the int indentifier.  Column 2 is "username" (varchar50) and is the primary key.  Column3 is "userID" (uniqueidentifier) and column 4 is "active" (varchar5) .

            Dim dc As New DataClassesDataContext
            Dim c3 As New Username With {.userID = userGuid, _
                                    .username = userCustomName, _
                                    .active = "False"}


        Catch ex As Exception
            Throw ex
        End Try


>>>Check duplicate while inserting record from Texbox.

Hi, On inserting record, I want to check that for example if a user enters a telephone number for any record is already in the database then it should validate that the number already exists in table and you cannot enter a duplicate value. Then if user will provide some other telephone number then he allows to insert record. For insertion of record I used ObjectdataSource. User will enter telephone number in a simple textbox. I may want to inform that I am working in asp.net2.0 and also not allowed to use any third party control or ajaxtoolkit. Any idea would be appreciated.

Problem Select ancestors a Record

Hi; I have a table by three columns ("ID","Title","ParentID"),me want to select ancestors a Record by "ID" identifier. example: ID      Title       ParentID 1         Library       null 2         Section1      1 3        Section2       1 4        1.1              2   for example if record 4 selected return records by "ID" identifier 2,1 How can me do it?  

Problem inserting into XML column

I have a C# application that inserts XML into Sql Server 2005.  When the message is more than 300 lines or so, it doesn't make it there.  The data shows in the application variables (stringWriter) but doesn't get inserted and that's confirmed looking in Profiler. The stored procedure is simply:          usp_WS_OrderEntry_InsertWSOrder @doc=@p1,@WSOrderId=@p2 output       INSERT INTO WS_OrderEntry_Order (OrderXML)       VALUES(@doc)  ..... C# .....             tableAdapter.usp_WS_OrderEntry_InsertWSOrder(stringWriter.ToString(), ref wsOrderId); .... In any cases, the variable "stringWriter" contains the data (as text). In profiler for a successfull call: declare @p1 xml set @p1=convert(xml,N'<OrderHeader><CustomerNumber>1234</CustomerNumber><Pr.....</PO></ShipToName> declare @p2 int set @p2=1077 exec dbo.usp_WS_OrderEntry_InsertWSOrder @doc=@p1,@WSOrderId=@p2 output select @p2 In unsuccessfull call: It shokes on the convert part and doesn't display any data (and doesn't insert as well).  I looked in LOGs, events and didn't find anything. Any help appreciated.  Thanks.

Page_Load Order -- Inserting a record

Is there any way to make sure an Insert Command happens before the Page_Load Events? I'm having a problem with inserting, then reading the DataSet directly after.  It seems it processes too quickly, or prior to the record being inserted. It has an impact on the DataSet that is to be displayed on screen. Summary:  Page_Loads > Finds Saved Data > If None exists (string contains 2 types of matches) > Displays the 1 of 2 types randomly. Buttons 1 & 2 Insert their respective options > Page Posts Back > Page_Loads > Read Saved Data > If my Max amount of the 1 of 2 types of Data are reached, the remaining type is shown > Displayed on page The problem I am having is that my DisplayDataSet is not getting the correct values.  It seems it's always 1 record behind on the count, because the count happens so fast, it's not getting the record that was just inserted. How can I write this, or make sure the "Find Saved Data' Routine happens after the Insert has been preformed?  Imports System.Data Imports System.Data.SqlClient Partial Public Class Assessment1 Inherits System.Web.UI.Page Protected Sub btnINS1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnINS1.Click Dim connetionString As String Dim connection As SqlConnection Dim adapter As New SqlDataAdapter Dim s

Problem Facing While Inserting records into table which os dynamicaly created

Hi,I have created table into database dynamicaly,while inserting records into that dynamic table i faced problem.The name "STD000001" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. this error occured.I created dynamic table by ,declare @STUDENT_Table varchar(50)set @STUDENT_Table = [dbo].[GetStudentTable](@SchoolID)declare @cmd varchar(5000)Set @cmd = 'Create Table  ' + @STUDENT_Table + '(   [student_id] [varchar](10)NOT NULL,    [school_id] [varchar](50)NULL,    [class1] [varchar](50)NULL,    [division1] [varchar](50)NULL,    [educalis_id] [varchar](16)NULL,    [roll_no] [varchar](10)NULL,    [first_name] [varchar](50)NULL,    [middle_name] [varchar](50)NULL,    [last_name] [varchar](50)NULL,    [gender] [varchar](6)NULL,    [father_name] [varchar](50)NULL,    [mother_name] [varchar](50)NULL,    [father_office_address] [varchar](200)NULL,    [mother_office_address] [varchar](200)NULL,    [home_address] [varchar](200)NULL,    [father_mobile_no] [varchar](10)NULL,    [mother_mobile_no] [varchar](10)NU

Problem when Inserting xml data into a table using OpenXml

Hi, i stuck with a problemam trying to insert data from xml file to sql server.i made query like thisSET NOCOUNT ON DECLARE @hDoc INT EXEC sp_xml_preparedocument @hDoc OUTPUT, '<NewDataSet>  <xs:schema id=''NewDataSet'' xmlns='''' xmlns:xs=''http://www.w3.org/2001/XMLSchema'' xmlns:msdata=''urn:schemas-microsoft-com:xml-msdata''>  <xs:element name=''Job''>     <xs:complexType>      <xs:sequence>        <xs:element name=''BillToNo'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''Colors'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''CompanyNo'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''CsrCode'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''CsrName'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''CustomerPoNo'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''DivisionOriginal'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''EstimateNo'' type=''xs:string'' minOccurs=''0'' /> <xs:element name=''FinalDeliveryDate'' type=''xs:string'' minOccurs=''0'' /> <xs:element name

Problem getting same record when trying to populate data into texboxes when selecting a row/record f


I can't seem to get the select record from the datagrid to populate in the textboxes. I get the same record no matter what I select in the datagrid. Hopefully someone can help with this. I tried using a where clause for POPULATION_LIST_ID = '" & Me.lblPopulationID.Text & "' and I get a ORA-01722 error - invalid number. Here is the code I have so far for the grid:

  Protected Sub MHGrid_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles MHGrid.SelectedIndexChanged
Me.Table1.Visible = True
Me.MHGrid.Visible = True

Dim dvSql As DataView = DirectCast(MHFoundation.Select(DataSourceSelectArguments.Empty), DataView)

For Each drvSql As DataRowView In dvSql
Me.lblPopulationID.Text = drvSql("POPULATION_LIST_ID").ToString
Me.txtMRN.Text = drvSql("MRN1").ToString()

problem with inserting records??


When i am inserting my data i am getting this error 

An explicit value for the identity column in table 'emp' can only be specified when a column list is used and IDENTITY_INSERT is ON.

i have set Identitty specification and is identity of the table as yes so i am getting this problem whenevr i set it to no i am not getting any error,can anyone explain me why i am getting this error??

trigger problem inserting null values??


I have created 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

USE [mine]
/****** Object: Trigger [dbo].[insert2]  Script Date: 10/03/2010 15:00:21 ******/
ALTER trigger [dbo].[insert2] 
  on [dbo].[emp] 
  after insert 
  declare @sno int,@result varchar(50),@empid int,@name varchar(50) 
select @empid=empid,@name=@name from emp  
 insert into triglog values(@empid,@name)  
but when

sql count record problem


if i have a prodcut with product no. and each product is made by many parts with parts no.
now i would like to retrieve data to count how many parts to make a product.
in sql, can i count parts no. by each product no. like belows:

select product_no,parts_name,manufacture_date,(select count(*) from item where product_no=p.product_no) as [No. of items] from prodcut p left outer join item i on p.product_no=i.product_no

but i found that the execution time is too long, is it the right way to count no. of item in each product??

Problem inserting ID value from one SQL table to another using CreateUserWizard



I am designing a registration form where I use CreateUserWizard. I am inserting data into two different tables while use is created. First table is Categories (columns: CustomerID, CatCode) where I insert CatCodes based on selections made in CheckBoxList and second table is Customers (columns: CustomerID, FName, LName, Email) where I register personal details (first, last name, email etc). CustomerID is generated automatically when client registers.

What I want to do is to connect Categories and Customers tables so when client selects CatCodes during registration and selected values are written into the Categories table CatCode column, appropriate CustomerID to be written there as well (in CustomerID column which is a ForeignKey from Cusomers table).

For example: Client is registered, his CustomerID (1001) is created during registration. He also selected three categories (1,2,3) which are written into the Categories table. Now as I have CustomerID in Categories Table as well (ForeignKey from Customer Table CustomerD column), I want generated CustomerID to show up in Categories table so i could later identify what catgories did client 1001 selected.

Category codes (CatCode) is inserted using code behind file in C#:

 protected void CreateUserWizard1_CreatedUser(obje

Problem when creating a Record Center in SharePoint 2010


Hi Guys

I have a problem when creating a Record Center in SharePoint 2010 on a publishing Portal. The Record center creates succesfully, but when I try to upload a record sharepoint says that the list does not exist, also when I try to go into the site settings of the record center I get the same issue. When trying to specify the libraries in the record centre management page....same thing! What could this be? 

Problem in inserting another entity via overriding insert method


I have problem to inserting another entity in overriding method of first entity my code sample is like bellow :

partial void InsertEntity1 ( Entity1 instance )


     Entity2 x = new Entity2() ;

tihs.Entity2s.Add(x) ;





in inserting Entity2 I get Exception "The Operation Cannot be perform for the entity because it is not being change track"


Problem inserting a null value into date column in SQL table


Hi all,

I am having a hard time inserting a null date into a SQL table.  Please be aware that I am using the Microsoft Application Data Blocks and not pure ADO.net.  I find a million examples of how to do this with ADO.net but cannot find anything about this using the MS Data Application Blocks.

Here is my VB.net code:

Save(Session("ref_no"),  txtdischarge_date.Text) 

Sub Save(ByVal ref_no As Integer, discharge_date As Date)

        SqlHelper.ExecuteNonQuery(connString, ref_no, discharge_date), "MyTableInsert")

End Sub

The textbox txtdischarge_date can either be a valid date or blank.  If it contains a valid date then the insert works properly.  But if the textbox  is blank, then I want to insert a null value into my date column.  I cannot get this to work.  I get this error when I run this and txtdischarge_date is blank:

Conversion from string "" to type 'Date' is not valid

Here is the code for my stored procedure, MyTableInsert:

ALTER Procedure MyTableInsert
   (@ref_no int,
    @discharge_date datetime = NULL)

Workflow. Copy item to new list - problem updating additional fields in the new list record




I'm having a couple of problems


I have a list, List A. On a couple of triggers I want list A to copy fields to a new record in List B.

Many of the columns are the same Say, column1, column 2, colmn 3. The Copy Item is working fine.


I then want to amend several fields in the new record in List B to reflect the new info from list A, and create info in a fields (say ListBcolum4 type choice and ListBcolum5 single line).

For the life of me I cant get this to work... ive tried updating fields, creating items...

A pointer on how to do this would be greatfully received.

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