.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

SQL Null Insert fails

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
I am developing an SSIS package and trying to update an existing table with info from new columns.  In other words, this table already has records for my input CSV file.  But this CSV file has additional columns for these same records.  So I need to keep existing columns in table.  How can I achieve this? I began by using Import/Export Wizard and it did most of hard work for me.  But one of the columns previously defined in this table doesn't allow NULLs.  So although I want these new rows to map into existing table, it seems like this will just overwrite the table values and I get the error below.  How can I fix this? I have a Flat File CSV source, Data Conversoin, and DestinationConnectionOLEDB Error: 0xC0202009 at Data Flow Task 1, Destination - j5c_ALL_DATA2 [138]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'timestamp', table 'berkeley.dbo.j5c_ALL_DATA2'; column does not allow nulls. INSERT fails.". Error: 0xC0209029 at Data Flow Task 1, Destinati

View Complete Post

More Related Resource Links

how to insert null value

hi, All   hear is my statement i am trying to insert null value through statement if (txtBankAcNo.Text == "") { sSqlDbCommand.Parameters["@BankAcNo"].Value = null; }                         else { sSqlDbCommand.Parameters["@BankAcNo"].Value = txtBankAcNo.Text; } but is throwing Error: expects the parameter '@BankAcNo' which is not supplied   Regards!   Hitesh    

how to in Insert database NULL if drop down list item is not selected ?

hi,   on my aspx I have the below DDL   <asp:DropDownList ID="ColorDDL" runat="server" DataSourceID="ColorObj"                                                 DataTextField="Size" DataValueField="BustId" Width="150px"                                                 AppendDataBoundItems="True">                                                    <asp:ListItem Value="-1">(Optional)</asp:ListItem>                             &n

Replication Error - Can't Insert Null into MSSubscription_agents.allow_subscription_copy

In attempting to set up a transactional replication subscription via the UI, I keep getting the error that I cannot insert a Null into the MSSubscription_agents table.  Originally, this was on the allow_subscription_copy column.  One resource suggested altering the column to allow Nulls but that only moved the issue to the attach_state and, later, attach_version columns.  My immediate issue was resolved with these changes BUT I'm really concerned that I need to alter a system table in order to allow a fairly simple replication subscription to be configured.  Has anyone run into this issue and is there a better work around available? I'm running SQL 2008 Enterprise Edition SP1 in a 64-bit environment on both the Publisher/Distributor and Subscriber servers.

how to insert null or empty string to datetime var ?

hi i get data from xml file and sometime the date is empty. i have this code:     try { TimeTo = Convert.ToDateTime(R[15].ToString()); }         catch { TimeTo = null ; } but i got error because i cant insert null to datetime var what i can do ? thak's in advance

SCOPE_IDENTITY() returns null after paramerized INSERT


I have a very simple database, without any triggers at this point. One of my tbales has a auto increment key (IDENTITY field). As the record has a string attribute, which valeu is received from the user, I insert a new records with a parametized INSERT statement. After insertion I try to fetch the new record's ID with the sttament SELECT SCOPE_IDENTITY(), but ti return null - while SELECT @IDENTITY and SELECT IDENT_CURRENT returns the valid values.

Moreover, if - for testing purposes - I execute the same insert without parameters, (i.e. providing the string value directly in the INSERT statement), then even SCOPE_IDENTITY() returns the correct value.

I didn't find any reference in the documentation regarding such restriction. Is is a known bug or the result of some misunderstading?

Error at Infopath after deleting a listfield - Cannot insert the value NULL into column 'tp_DocId'



after deleting an unused calculated field id_2 at my sharepoint list I get the following error when creating a new item with my Infopath 2010 form (update item still works):
"The form cannot be submitted.
Exception from HRESULT: 0x80131904"

The joblog tells me:
09/28/2010 13:24:46.96  w3wp.exe (0x093C)                        0x0C7C SharePoint Foundation          Logging Correlation Data       xmnv Medium   Name=Request (POST:http://test-project.xx.intranet:80/project/ideas/_layouts/Postback.FormServer.aspx) f8ffa9c3-f81c-429b-91de-3f76739e446a
09/28/2010 13:24:47.03  w3wp.exe (0x093C)                        0x0C7C SharePoint Foundation          Logging Correlation Data       xmnv Medium   Site=/project f8ffa9c3-f81c-429b-91de-3f76739e446a
09/28/2010 13:24:47.19  w3wp.exe (0x093C)               &nbs

Trying to Retain Values in a FormView When Validations fails or the SQL Fails on an Insert


To me this should be much easier, but I can't seem to retain the values that are put in the Insert template upon a validation failure or when the sql fails. This will prevent the user from having to retype everything agin in the event of a failure.

As you can see I am using the Sub Insert_Click and not using an insert through the wizard. so the fields are unbound textboxes on the form. There are 2 dropdowns that provide choices for the other fields.

Any help would be appreciated. I pasted in the code using the text only option becuase the format was getting messed up. I porboably need more background on why the fields are blanking out.



See Code below



Protected Sub InsertButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)

' Dim sqlparm As String

Dim con As Data.SqlClient.SqlConnection = Nothing

Dim cmd As Data.SqlClient.SqlCommand = Nothing

Dim dr As Data.SqlClient.SqlDataReader = Nothing

Dim sql As String = ""

Dim x As Integer

' Dim x As Integer



If CType(Me.fvwAppSecurityDetails.FindControl("CorpIDTextBox"), TextBox).Text = "" Then

Me.lblMessage.Text = "CorpID cannot be blank"

Exit Try

Else : Page.Session("CorpID&

DetailsView - Cannot insert the value NULL into column


Can somebody tell me why I am getting this error when I try to insert data in a DetailsView 2.0? 

Cannot insert the value NULL into column 'Title', table 'C:\INETPUB\WWWROOT\TEST\APP_DATA\QUIZ.MDF.dbo.Question'; column does not allow nulls. INSERT fails.

I used the Configure Data Source wizard to set this up and Question ID is a autonumber.

When you autogenerate the Insert button on a Detailsview control and click on that button during run time the control posts back with text input areas where you are supposed to type the Title, Answer1, Answer2 etc. Obviously I typed data in to this text area labeled Title. It didn't work and that's why I'm asking about the control. Because I can't debug the control I am having a hard time figuring out where the problem is.

Here is the code and thanks for any help.

<asp:DetailsView ID="EditDetails" runat="server" Height="50px" Width="550px" AutoGenerateRows="False"  AutoGenerateInsertButton="True" AutoGenerateEditButton="True" AutoGenerateDeleteButton="True" CellPadding="4" DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None" AllowPaging="True">
                        <FooterStyle BackColor="#5D7B9D" Font-Bol

JDBC PreparedStatement insert fails with no exception

We are calling executeUpdate() on a PreparedStatement to do an insert.
The call does not throw an exception but the value returned is -1 which indicates that no rows were inserted.

We were using JDBC 2.0, but upgrading to 3.0 made no difference.

The instance in question is a Developer Edition with this version:

Microsoft SQL Server 2008 (SP1) - 10.0.2757.0 (X64)   Jan  8 2010 19:55:08  
Developer Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)

Using a different instance works as expected. Its version is:

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52  
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

Executing an insert from SSMS into the table in the problem instance works just fine.

JVM version is

java version "1.6.0_13"
Java(TM) SE Runtime Environment (build 1.6.0_13-b03)
Java HotSpot(TM) Client VM (build 11.3-b02, mixed mode)




I am working on BUlk Insert from a file to a table in SQL server 2005.



EmpName Varchar(20),

EmpAge Varchar(3),


INSERT NULL value in SQL database when there is no file to upload



I've been working on a code that allows users to upload images (jpgs) as an optional field. Alhough the uploading works, I'm struggling when the user doesn't have an image to upload. I can't seem to find the way to NOT insert binary data into my database, I just want an NULL value when there is no image.
I get this error message: ...expects parameter @Image, which was not supplied.


Here is the code: 

Sub InsertItem()

Dim imageData As Byte() = Nothing
Dim MIMEType As String = ""


Insert NULL values into Integer column in Access 2007 tables

hi  there:

  My source data  is below and it's in CSV format

Days waited


My destination is a Number column in a 2007 access table  and the required property is set to NO

First question :

   I used a Data conversion task in DFT to convert [DT_STR] to Integer as SSIS seems to default the source as DT_STR type. However, SSIS keeps complaining that

"The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data."

I know that's because of those NULL values. how to solve this issue?


--Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

Insert null datetime into SQL DB


Hi There,

I have 2 textboxes for BEGINNING DATE and END DATE.
First I compare the dates so that the END DATE is bigger than the BEGINNING DATE. This is OK with the validators.

If the END DATE´S TEXTBOX is empty, then it should insert into the SQL DB, a null value. The field has a DATETIME type.
Since my INSERT Query requires a parameter when i call the ADD METHOD, it returns that the END DATE parameter is required.

Thank you.

Cannot insert the value NULL into column 'Cid


Hello there

I have a table called category that has tow attribute (Cid,Name) 

what can i do to insert  category name only without allow user to insert category id in my webpage, I use view detail control and make the wizard link but when run the error message appear (Cannot insert the value NULL into column 'Cid)

note the identity properties is not pass.

thank you.

Cannot insert NULL into a ntext type column ? Using SQL CE product version 3.5.8080.0


int          not null 
, name         nvarchar(50)  not null 
, addrLine1    nvarchar(50) null 
, addrLine2    nvarchar(50) null 
, suburb       nvarchar(30)

Passing a Null as Parameter fails



I have a simple Stored Proc that has a single parameter passed.  If the parameter is Null the SP should return all records else it should return the record for the passed parameter.  BUT it fails on passing Null.  I have used the exact same format for other SP's which work fine, but never passing a Null.

Anyone got suggestions?

-- =============================================
-- Author:		<Phillip J Seaman>
-- Create date: <05/17/2011>
-- Description:	<Gets ALL Vessel names OR single By ID value>
-- =============================================
	-- Add the parameters for the stored procedure here
	@V_ID int = Null

Insert value using Table Value Functions

a real gem in Sql Server 2008. mostly people still using Stored procedure may be they shifted to SQL Server but they are not using TVF right now.
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