.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

trigger problem inserting null values??

Posted By:      Posted Date: October 03, 2010    Points: 0   Category :Sql Server

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

View Complete Post

More Related Resource Links

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)

How to deal with NULL values in a SQL table


 Hi all

In order to add/update/delete data from a table I build 2 classes: 1 class containing all the table fields and another class with methods to add/insert/update/delete records. I am learning how to avoid errors when your table must use SQL NULL values. One error is when I call my UPDATE method (see below):

  public void UpdateItem(OcItemConstraintDetails item)
  SqlConnection con = new SqlConnection(connectionString);
  SqlCommand cmd = new SqlCommand("ItemConstraints_Update", con);
  cmd.CommandType = CommandType.StoredProcedure;

  cmd.Parameters.Add(new SqlParameter("@itemConstraintId", SqlDbType.Int, 4));
  cmd.Parameters["@itemConstraintId"].Value = item.ItemConstraintId;

  cmd.Parameters.Add(new SqlParameter("@path", SqlDbType.NVarChar, 150));
  cmd.Parameters["@path"].Value = item.Path;

if you watch the SQL profiler trace you will see that I am passing a value of 'default' ;so that causes me problems.

exec ItemConstraints_Update @itemConstraintId=1,@path=default

With that said, what are the best practices when your SQL table accepts NULL values and when:
- you are passing an empty value to your stored procedure;

passing object values from server to client problem


I get a message that x_login is not declared.  I added Dim login as String = "123", but that didn't help.  What's the problem?

code behind....

Dim Login = "123"
x_login.Value = loginID


<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<form id="simForm" runat="server" method='post' action='https://test.authorize.net/gateway/transact.dll'>
        <input id="HiddenValue" type="hidden" value="Initial Value" runat="server" />
        <input type='hidden' runat="server" name='x_login' id='x_login' />
        <input type='submit' runat="server" id='buttonLabel' />



Help: Too many lines of code to achieve a simple goal: keep my NULL SQL values...


Hello all

What is the best way (best practice) to preserve my NULL SQL values in the database
during an insert/update operation (without receiving cast invalid errors)?
Also, how to display a string 'n/a' when a sql value is NULL?

My project is using FormView with Edit/Insert templates and classes to represent my tables

Goal1....: display 'n/a' when there are NULL SQL values in the database
Solution1: I am using ISNULL(field,'') in my store procedure for SELECT statements

Goal2....: if the fields are 'n/a', then save them back in the database as NULL
Solution2: on my insert/edit methods I am having to check the values being passed, i.e:

cmd.Parameters.Add(new SqlParameter("@scope", SqlDbType.NVarChar, 50));

if (item.Scope == "n/a")
 cmd.Parameters["@scope"].Value = DBNull.Value;
 cmd.Parameters["@scope"].Value = item.Scope;

Not to mention that for SQL DateType fields when you use ISNULL(field,'') the return string is '1/1/1900 12:00:00 AM'
and I have to change the field value in every field that represents a date, i.e:

protected void FormView1_DataBound(object sender, Sy

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.

Problem getting values next to each other from two separate CheckBoxList controls

Hi, I am using three CheckBoxList controls in ASP.NET Wizard control. CheckBoxList controls: <asp:CheckBoxList ID="cblLondonDates" runat="server" DataSourceID="SqlDataSource1" DataTextField="ShowDate" DataTextFormatString="{0:G}" DataValueField="ShowDate" ForeColor="#333333" CssClass="ShowDateMargin"></asp:CheckBoxList> <asp:CheckBoxList ID="cblSingleL" runat="server" DataSourceID="SqlDataSource2" DataTextField="size8" DataValueField="size8" ForeColor="#333333"> </asp:CheckBoxList> <asp:CheckBoxList ID="cblHalfL" runat="server" DataSourceID="SqlDataSource2" DataTextField="size6" DataValueField="size6" ForeColor="#333333"></asp:CheckBoxList>   cblLondonDates chackboxlist control shows dates generated from SQL table. cblSingleL an cblHalfL show table sizes. On ASP page they come up as following: 12/03/2011(checkboxlist-ID="cblLondonDates")-Single(checkboxlist-ID="cblSingleL") - Half(checkboxlist-ID="cblHalfL") 03/03/2012(checkboxlist-ID="cblLondonDates")-Single(checkboxlist-ID="cblSingleL") - Half(checkboxlist-ID="cblHalfL") 05/10/2010(checkboxlist-ID="cblLo

problem with passing parameter values to stored proceedure from asp.net c#

hi all, i am facing a problem while passing parameters to a stored proceedure. following is my sql query written for creating this stored procedure:   CREATE proc [dbo].[getstudent]( @wherecolumn nvarchar(2000), @wherevalue nvarchar(2000)) asbegin  declare @sql nvarchar(max) set @sql = 'select * from student2 where ' + @wherecolumn+'='+@wherevalueexec sp_Executesql @sql end   my table contains four columns: And the sql Query For the table is as follows:   CREATE TABLE [dbo].[student2]( [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [rollno] [int] NOT NULL, [class] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [section] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_student2] PRIMARY KEY CLUSTERED ( [rollno] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]   from page behind I am passing the values to parameter as follows:   SqlCommand command = new SqlCommand("getstudent", c.con); SqlDataAdapter datadapter = new SqlDataAdapter(c.cmd); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@wherecolumn", SqlDbType.NVarChar).Value = "rollno"; command.Parameters.Add("@wherevalue", Sq

Problem with CQWP "There is a problem with one or more of the field values below"

Hi, I'm trying out the content query webpart combined with content types. First I created a new site collection. In this site collection, these are the steps I did: - I enabled "SharePoint Server Publishing Infrastructure" feature at the site collection level - I enabled "SharePoint Server Publishing" feature at the site level - I created a new site column "Is Meat Eater", which is a yes/no field and added this to a new custom group called "Zoo" - I created a new content type called "Zoo Animal" that inherits from "Item" in category "List", and added this to a new custom group called "Zoo" - I added my site column "Is Meat Eater" to this content type (so now I have Title + Is Meat Eater) - I created a new custom list called "SharePoint Zoo" - I enabled content types on this list - I added "Zoo Animal" from the existing content types - I removed the default content type "Item" from the list (so now only 1 content type left, my own) - I added a few animals in the list - I added a content query web part on the homepage - As source I chose "Show items from all sites in this site collection" - As List Type I chose "custom list" - As Content Type Group I chose "Zoo" - As Content Type I chose "Zoo Animal" - I hit apply at

Problem with date values in data-driven subscription reports

Hello together, I have a question in regards of data-driven subscription report. I already have an ad-hoc report in place which can be customized by date etc. I now want to run these reports with a subscription. My challenge is now that I don't know how I can pre-define the date. I basically want the start date of NOW() and the end date 7 days in the past. I was looking around but did not get any clue where and how I have to configure it the way it works. I found this thread but honestly do not understand it well... :( http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/fb48fd15-09dd-4f5f-a09f-d92bf304c1a2 Can someone help me out please? M.

Problem With Using Trigger for Setting Label Content

Basically, I have a label and depending on the Object type of the current data source item I want to control what the content of the label is. The trigger I am trying is: <Label Margin="222,21,242,134" Name="MyLabel"> <Label.Resources> <DataTrigger Binding="{Binding Path=/EntryType}" Value="Object.Type1" x:Key="myTrigger"> <Setter TargetName="MyLabel" Property="Content" Value="{Binding Path=/Object.Property1}"/> </DataTrigger> </Label.Resources> </Label> The problem is that I am getting this error: 'Content' member is not valid because it does not have a qualifying type.   Do I need to use some sort of converter or something? Or am I aproaching this worng?

SSRS Report based on SSAS Cubes doesn't show NULL values correctly

I have a SSRS report which is based on an SSAS Cube. In the Cube the formating of numeric fields that NULL values are properly shown as NULL values. In the Cube Browser and Excel this is shown correctly. However when I design the report already the query against SSAS shows these fields as "0" instead of NULL. Is there a way to fix this or is this a bug ?  

Gridview RowCommand Event returning null values

HiI'm trying to retreive values entered by users in the textboxes in the footer of the gridview and insert these values into another sql table.My code is as follows :-protected void GridView2_RowCommand(object sender, GridViewCommandEventArgs e)    {       if(e.CommandName.Equals("addnew"))       {                       TextBox t1 = (TextBox)GridView2.FooterRow.FindControl("tfrm");            string frm = t1.Text;            TextBox t2 = (TextBox)GridView2.FooterRow.FindControl("tsub");            string sub = t2.Text;            string cmd = "insert into suggestions values('" + frm + "','" + sub + "','c','c')";            con.Open();            SqlCommand com = new SqlCommand(cmd,con);            int a = com.ExecuteNonQuery();&n

Comparing Null Values(from variable) in SSIS

Hi All I need to create a script that checks to see if a variable value is NULL, if it is then it executes the next SQL Task and if not then the package has finished. I have this in a SQL 2000 DTS package script: Function Main() If isnull(DTSGlobalVariables("NextLoadDate")) Then Main = DTSStepScriptResult_DontExecuteTask Else Main = DTSStepScriptResult_ExecuteTask End if End Function As you can see, all I want to do is check to see if a variable is null then don't execute the next task. But i cannot get it to work in a SQL 2005 SSIS package, so far I have created a script and come up with the following: Public Sub Main() If Dts.Variables("NextLoadDate").Value.ToString <> "" Then Dts.TaskResult = Dts.Results.Success Else Dts.TaskResult = Dts.Results.Failure End If End Sub Any Ideas? Thanks BigGopher

Inserting List Item values from a Visual web part

Hi Geeks, I am struck here with one situation that is: 1) I created a enquiry list. 2) Then I created a visual web part it contains the same fields as enquiry list  as textboxes for entry. If any body enters the data and click submit. Those data must be reflected into the enquiry list. Please enlighten me how can I do this.   Thanking you.

Comparing columns with NULL values--Merge says unmatched when data is matched.

I found the article below describing this same issue with the Oracle merge statement.  It also described a work-around:    http://searchoracle.techtarget.com/tip/Merging-datasets-with-NULL-values I have been unable to find a solution to this issue for SQL Server 2008.  I am trying to do basic ETL from Staging table to a Type II dimension table in a Data Warehouse.  I am using the Merge statement with the Unique key minus the modification date as my merge key list.  I have a handful of columns (in both Staging and the DW) containing nulls in my merge key list.  I have tried the ISNULL function and the ANSI_NULL db option (OFF) with no success.  When I put zeros in the null columns, it works as expected.  I am about to try separate INSERT and UPDATE statements, but I fear that the NULL comparison issue will bite me there with the JOIN statement.  Has anyone else experienced this?  NULL values are valid in our Data Warehouse.  How can I make T-SQL handle them? Thank you for your help, Jesse

Logon Trigger blocking problem

I'm trying to learn how to use microsoft sync framework using the following example, but am having a problem with a logon trigger blocking something.  If I disable the trigger it will work, but that really isn't an option.  Normally, if something is blocked it is placed in a blockedLog table with the name of the user and application.  If we need to allow access then we can put this information in the BlockedExceptions table to allow it through.  Initially, the .Net SqlClient Data Provider gets blocked.  After adding it to the accepts table, I still get a blocking error due to the trigger, but I can't determine what is actually being blocked.  The table never fills with any information about what gets blocked after fixing the initial .Net SqlClient Data Provider error and I have not been able to figure out anything using the profiler or activity monitor. Sync Example in question:http://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=sync&ReleaseId=3422 CREATE TRIGGER [BlockLogins] ON ALL SERVER WITH EXECUTE AS 'DBTrigger' FOR LOGON AS BEGIN DECLARE @Allow bit SET @Allow = 'TRUE' IF (SELECT COUNT(*) FROM DB.dbo.Programs WHERE AppTitle= APP_NAME()) = 0 BEGIN -- ** Other allowed apps and users are in BlockedExceptions ** -- If it is a non-application that any user is allowed to run then let it through.
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