CODEWITHSUNDEEP
c#sql-serversql-server-2008datatable
JRoy
JRoy

Reputation: 47

Passing a Datatable as type to stored procedure cannot insert null value

So I have the following C# method to upload a DataTable to a SQL Table (Sql Server 2008)

public bool ImportData(DataTable dtImport, string _importtype, string _custno)
{
    bool success = false;
    string constr = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlConnection con = new SqlConnection(constr);
    try
    {
        con.Open();
        if (con.State == ConnectionState.Open)
        {
            SqlCommand cmdProc = new SqlCommand("JR_SP_ImportDatatable", con);
            cmdProc.CommandType = CommandType.StoredProcedure;
            cmdProc.Parameters.AddWithValue("@Details", dtImport);
            cmdProc.Parameters.AddWithValue("@Type", _importtype);
            cmdProc.Parameters.AddWithValue("@Custno", _custno);
            cmdProc.ExecuteNonQuery();
            success = true;
        }
    }
    catch (Exception ex)
    {
        string msg = "Fetch Error:";
        msg += ex.Message;
        throw new Exception(msg);
    }
    finally
    {

    }
    return success;
}

I have also created a User-Defined Table Type as follows...

CREATE TYPE [dbo].[ImportTable] AS TABLE(
[fname] [varchar](max) NULL,
[lname] [varchar](max) NULL,
[date1] [varchar](max) NULL,
[fname2] [varchar](max) NULL,
[lname2] [varchar](max) NULL,
[date2] [varchar](max) NULL,
[salutation] [varchar](max) NULL,
[name1] [varchar](max) NULL,
[name2] [varchar](max) NULL,
[company] [varchar](max) NULL,
[address] [varchar](max) NULL,
[address2] [varchar](max) NULL,
[city] [varchar](max) NULL,
[state] [varchar](max) NULL,
[zip] [varchar](max) NULL,
[custno] [varchar](max) NULL
)
GO

Do note that on my Sql table I do not allow NULL values in any of these fields so when they are blank in the Datatable the default is ('') so it inserts a blank value.

My issue is that it throws an error "Fetch Error:Cannot insert the value NULL into column 'fname2', table 'dbo.UsersMailingData'; column does not allow nulls. INSERT fails. The statement has been terminated.

However when I am debugging the Datatable I am sending to the stored procedure it has the column fname2 with blank values. Any reason it would think it is null? To keep it short here is my fname2 field only in my sql table...

[fname2] [nvarchar](max) NOT NULL

with this constraint

ALTER TABLE [dbo].[UsersMailingData] ADD  CONSTRAINT [DF_UsersMailingDataFinal_fname2]  DEFAULT (' ') FOR [fname2]
GO

Lastly here is my stored procedure I am using to insert the records...

CREATE PROCEDURE [dbo].[JR_SP_ImportDatatable]

    @Details ImportTable Readonly,
    @Type varchar(max),
    @Custno varchar(max)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for procedure here
    DECLARE @rownum int
    IF EXISTS(SELECT * FROM UsersMailingData WHERE custno = @Custno)
        SET @rownum = (SELECT MAX(recip_id)+1 FROM UsersMailingData WHERE custno = @Custno)
    ELSE
        SET @rownum = 0

    IF(@Type = 'Add')
    BEGIN
        INSERT INTO UsersMailingData(recip_id, fname, lname, date1, fname2, lname2, date2, salutation, name1, name2, company, address, address2, city, state, zip, custno)
        SELECT row_number() over (order by custno)+@rownum, fname, lname, date1, fname2, lname2, date2, salutation, name1, name2, company, address, address2, city, state, zip, custno FROM @Details
    OPTION (RECOMPILE);
    END
    IF(@Type = 'Replace')
    BEGIN
        DELETE FROM UsersMailingData
        WHERE Custno = @Custno
        INSERT INTO UsersMailingData(recip_id, fname, lname, date1, fname2, lname2, date2, salutation, name1, name2, company, address, address2, city, state, zip, custno)
        SELECT row_number() over (order by custno)+@rownum, fname, lname, date1, fname2, lname2, date2, salutation, name1, name2, company, address, address2, city, state, zip, custno FROM @Details
    OPTION (RECOMPILE);
    END
END


GO

If anyone has any ideas on why I would be getting this error it would be helpful.

Upvotes: 3

Views: 1777

Answers (1)

Brian Stork
Brian Stork

Reputation: 980

The column default will only apply if you do not supply a value for it. There is a difference between:

Insert TableWithThreeColumns(One,Two) values ('One','Two')
-- Third, unnamed column gets default

and

insert TableWithThreeColumns(One,Two,Tree) values ('One','Two',NULL)
-- Column will NOT get default: it will try to put in NULL

Instead of using default constraints, you can test for null from your source during your select:

insert into table (column1,column2)
select isnull(column1,''),isnull(column2,'')

Upvotes: 2

Related Questions