ASP.NET - "Cannot insert the value NULL into column 'firstName' . . ." when trying to do SQL INSERT

Question

Running .NET Version 4.0, I'm trying to INSERT into a table using the SqlDataSource.Insert() I get "Cannot insert the value NULL into column 'firstName' . . ."

The markup was generated by the sql wizard, except for the "DefaultValue=" which I added just to test. So with the DefaultValue= in place, the INSERT works, but with this removed I get the error message. The column that SQL server is complaining about is the PK for the table so cannot be nullable, but running the debugger, the field IS populated prior to the call to SqlDataSource.Insert().

Any ideas what I am doing wrong?

Here is the markup :-

<h2>
    <asp:Label ID="PageTitle" runat="server"></asp:Label>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ApplicationServices %>" 
        DeleteCommand="DELETE FROM [profiles] WHERE [firstName] = @firstName" 
        InsertCommand="INSERT INTO [profiles] ([firstName], [lastName], [dateOfBirth], [email]) VALUES (@firstName, @lastName, @dateOfBirth, @email)"
        SelectCommand="SELECT * FROM [profiles] WHERE ([firstName] = @firstName)" 
        UpdateCommand="UPDATE [profiles] SET [lastName] = @lastName, [dateOfBirth] = @dateOfBirth, [email] = @email WHERE [firstName] = @firstName">
        <DeleteParameters>
            <asp:Parameter Name="firstName" Type="String" />
        </DeleteParameters>
        <InsertParameters>
            <asp:Parameter Name="firstName" Type="String"  DefaultValue="Bri"/>
            <asp:Parameter Name="lastName" Type="String" DefaultValue="Moss"/>
            <asp:Parameter DbType="Date" Name="dateOfBirth"  DefaultValue="13/04/1961"/>
            <asp:Parameter Name="email" Type="String"  DefaultValue="bri@hotmail.com"/>
        </InsertParameters>
        <SelectParameters>
            <asp:Parameter DefaultValue="Ken" Name="firstName" Type="String" />
        </SelectParameters>
        <UpdateParameters>
            <asp:Parameter Name="lastName" Type="String" />
            <asp:Parameter DbType="Date" Name="dateOfBirth" />
            <asp:Parameter Name="email" Type="String" />
            <asp:Parameter Name="firstName" Type="String" />
        </UpdateParameters>
    </asp:SqlDataSource>
</h2>

Here is the call to SqlDataSource.Insert() . . .

protected void ProfileSubmit_Click(object sender, EventArgs e)
{
    Page.Validate();
    if (Page.IsValid) 
    {
        SqlDataSource1.Insert();
    }
}

Here is the error message :-

Server Error in '/' Application.
--------------------------------------------------------------------------------
Cannot insert the value NULL into column 'firstName', table 'C:\USERS\KATHARINA\DOCUMENTS\VISUAL STUDIO 2010\PROJECTS\HEALTHTRACK\HEALTHTRACK\APP_DATA\ASPNETDB.MDF.dbo.profiles'; column does not allow nulls. INSERT fails.
The statement has been terminated. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'firstName', table 'C:\USERS\KATHARINA\DOCUMENTS\VISUAL STUDIO 2010\PROJECTS\HEALTHTRACK\HEALTHTRACK\APP_DATA\ASPNETDB.MDF.dbo.profiles'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Source Error: 
Line 21:             if (Page.IsValid) 
Line 22:             {
Line 23:                 SqlDataSource1.Insert();
Line 24:             }
Line 25:         }

In the table you might have given not null constraint to the column "firstName". If you have SqlServerManagementStudio try to connect you database with it, and fire insert query with data(that you are supplying with this example), and see the results. — , Sep 20 '12 at 14:27

score 0 · Answer 1 · answered Sep 20 '12 at 14:27

0

What is the field bound to the (insert) parameter? When you say it works if you retain DefaultValue, that means the parameter meant for firstname was null or unresolved therefore the value in DefaultValue is being used.

Conversely if you don't provide a DefaultValue and the parameter is null/undefined, the insert will fail (because the db column restriction doesn't allow null).

See MSDN Sample

Hth

answered Sep 20 '12 at 14:27

EdSF

11,753
6
42
83

Hi EdSF, The field bound to the insert parameter is also called "firstName". I guess that the "@firstName" in the INSERT command is referring to this field on the form. I agree with your analysis entirely, but I just cannot see why the field is NULL when it is used as part of the INSERT command, since I have looked at the value of "firstName.Text" in the debugger immediately before the "SqlDataSource1.Insert();" . . . looking at the example you provided now, thanks for the suggestion. – mossy Sep 20 '12 at 16:46

score -1 · Answer 2 · answered Sep 20 '12 at 14:22

-1

mossy: it is not the problem of connection string, if it would be your database would not have connected in first place. It is connecting to database, that is why the error is caused that it can not insert a null value to your table. Allow null to the coloumn firstName in your database table.

answered Sep 20 '12 at 14:22

Deepika Thapa

63
6

Hi dpk_0702, agreed the connection would not have succeeded in the first place if the connection string was wrong. But I cannot make the "firstName" column nullable since it's the primary key to the table, plus the form textbox (also "firstName") DOES have a value when I try to perform the INSERT. Thanks for your suggestion though. – mossy Sep 20 '12 at 16:18
1

I think here we have the problem. A field like firstname shouldn't be the primary key. There are several rules for primary keys (look into some theory books about relational databases). And one is: the primary key should have no other meaning, beside being a primary key. So therefore I'd create a new field called 'id' which gets a simple id. And after that you can make the field firstname nullable. – reto Sep 20 '12 at 16:39
a short google for 'asp.net data tutorial' led me to this side: http://www.asp.net/web-forms/tutorials/data-access/editing,-inserting,-and-deleting-data/an-overview-of-inserting-updating-and-deleting-data-cs – reto Sep 20 '12 at 16:40

ASP.NET - "Cannot insert the value NULL into column 'firstName' . . ." when trying to do SQL INSERT

2 Answers2