none
Visual Studio | "Cannot insert the value NULL into column.." messages, despite the column having a value.. RRS feed

  • Question

  • Description

    I have an Azure Web App + SQL which includes an HTML web form. This is connected to my Microsoft SQL Server Manager Studio. Coding is through Microsoft Visual Studio.

    My Microsoft Visual Studio debugging dialogue, when completing a test submission of my WebForm, is telling me the following statement:

    Cannot insert the value NULL into column 'Fullname', table 'bluecitydb.dbo.tblFullname'; column does not allow nulls. INSERT fails.
    Cannot insert the value NULL into column 'Fullname', table 'bluecitydb.dbo.tblFullname'; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    The statement has been terminated.

    It's true that I have set up my database so that I do not allow NULL values to be entered, however, despite ALL the fields in my WebForm NOT having NULL values upon test-submitting my Webform, I still get these error messages. 

    Can MS VS not recognise the difference between a NULL value and a non-Null value?

    Any pointers would be much appreciated because this is making my blood boil. I'm genuinely getting so angry with this piece of software and I'm ready to take a hammer to my computer...

    Current Code

    SqlCommand insert = new SqlCommand("EXEC dbo.InsertDetails @Fullname, @Address, @Email", stormconn);
    insert.Parameters.AddWithValue("@Fullname", TextBox1.Text);
    insert.Parameters.AddWithValue("@Address", TextBox2.Text);
    insert.Parameters.AddWithValue("@Email", TextBox3.Text);
    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="StormWeb.WebForm1" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <asp:Label ID="Label1" runat="server" Text="Label">Insert Full Name</asp:Label>
                <br />       
                <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> <br />
                <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox> <br />
                <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox> <br />
                <asp:Button ID="Button1" runat="server" Text="Submit" OnClick="Button1_Click" />
            </div>
        </form>
    </body>
    </html>

    Screenshots/Pictures/Illustrations

    Below is my Webform, you'll note there are no NULL fields. This was what was submitted on the test. 


    What I’ve tried

    I have an ID column, this has been set to the primary key and that the "Identity Specification" has been set to "Yes".

    Debug/Diagnostic Messages

    'iisexpress.exe' (CLR v4.0.30319: DefaultDomain): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Drawing\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.dll
    An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code
    Cannot insert the value NULL into column 'Fullname', table 'bluecitydb.dbo.tblFullname'; column does not allow nulls. INSERT fails.
    Cannot insert the value NULL into column 'Fullname', table 'bluecitydb.dbo.tblFullname'; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    The statement has been terminated.
    
    'iisexpress.exe' (CLR v4.0.30319: /LM/W3SVC/2/ROOT-1-131940187829171158): Loaded 'C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\Common7\IDE\PrivateAssemblies\Runtime\Microsoft.VisualStudio.Debugger.Runtime.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
    The program '[21200] iisexpress.exe' has exited with code -1 (0xffffffff).

    Additional Information



    Software Used + Versions

    Microsoft Visual Studio Community 2017
    Version 15.9.6
    VisualStudio.15.Release/15.9.6+28307.344
    Microsoft .NET Framework
    Version 4.7.03056
    Installed Version: Community
    Application Insights Tools for Visual Studio Package   8.14.11009.1
    Application Insights Tools for Visual Studio
    ASP.NET and Web Tools 2017   15.9.04012.0
    ASP.NET and Web Tools 2017
    ASP.NET Core Razor Language Services   15.8.31590
    Provides languages services for ASP.NET Core Razor.
    ASP.NET Web Frameworks and Tools 2017   5.2.60913.0
    For additional information, visit https://www.asp.net/
    Azure App Service Tools v3.0.0   15.9.03024.0
    Azure App Service Tools v3.0.0
    Azure Data Lake Node   1.0
    This package contains the Data Lake integration nodes for Server Explorer.
    Azure Data Lake Tools for Visual Studio   2.3.3000.2
    Microsoft Azure Data Lake Tools for Visual Studio
    Azure Functions and Web Jobs Tools   15.9.02046.0
    Azure Functions and Web Jobs Tools
    Azure Stream Analytics Tools for Visual Studio   2.3.3000.2
    Microsoft Azure Stream Analytics Tools for Visual Studio
    C# Tools   2.10.0-beta2-63501-03+b9fb1610c87cccc8ceb74a770dba261a58e39c4a
    C# components used in the IDE. Depending on your project type and settings, a different version of the compiler may be used.
    Common Azure Tools   1.10
    Provides common services for use by Azure Mobile Services and Microsoft Azure Tools.
    Fabric.DiagnosticEvents   1.0
    Fabric Diagnostic Events
    JavaScript Language Service   2.0
    JavaScript Language Service
    Microsoft Azure HDInsight Azure Node   2.3.3000.2
    HDInsight Node under Azure Node
    Microsoft Azure Hive Query Language Service   2.3.3000.2
    Language service for Hive query
    Microsoft Azure Service Fabric Tools for Visual Studio   2.4
    Microsoft Azure Service Fabric Tools for Visual Studio
    Microsoft Azure Stream Analytics Language Service   2.3.3000.2
    Language service for Azure Stream Analytics
    Microsoft Azure Stream Analytics Node   1.0
    Azure Stream Analytics Node under Azure Node
    Microsoft Azure Tools   2.9
    Microsoft Azure Tools for Microsoft Visual Studio 2017 - v2.9.10730.2
    Microsoft Continuous Delivery Tools for Visual Studio   0.4
    Simplifying the configuration of Azure DevOps pipelines from within the Visual Studio IDE.
    Microsoft JVM Debugger   1.0
    Provides support for connecting the Visual Studio debugger to JDWP compatible Java Virtual Machines
    Microsoft Library Manager   1.0
    Install client-side libraries easily to any web project
    Microsoft MI-Based Debugger   1.0
    Provides support for connecting Visual Studio to MI compatible debuggers
    Microsoft Visual Studio Tools for Containers   1.1
    Develop, run, validate your ASP.NET Core applications in the target environment. F5 your application directly into a container with debugging, or CTRL + F5 to edit & refresh your app without having to rebuild the container.
    NuGet Package Manager   4.6.0
    NuGet Package Manager in Visual Studio. For more information about NuGet, visit http://docs.nuget.org/.
    ProjectServicesPackage Extension   1.0
    ProjectServicesPackage Visual Studio Extension Detailed Info
    ResourcePackage Extension   1.0
    ResourcePackage Visual Studio Extension Detailed Info
    ResourcePackage Extension   1.0
    ResourcePackage Visual Studio Extension Detailed Info
    SQL Server Data Tools   15.1.61901.03220
    Microsoft SQL Server Data Tools
    ToolWindowHostedEditor   1.0
    Hosting json editor into a tool window
    TypeScript Tools   15.9.20918.2001
    TypeScript Tools for Microsoft Visual Studio
    Visual Basic Tools   2.10.0-beta2-63501-03+b9fb1610c87cccc8ceb74a770dba261a58e39c4a
    Visual Basic components used in the IDE. Depending on your project type and settings, a different version of the compiler may be used.
    Visual F# Tools 10.2 for F# 4.5   15.8.0.0.  Commit Hash: 6e26c5bacc8c4201e962f5bdde0a177f82f88691.
    Microsoft Visual F# Tools 10.2 for F# 4.5
    Visual Studio Code Debug Adapter Host Package   1.0
    Interop layer for hosting Visual Studio Code debug adapters in Visual Studio
    Visual Studio Tools for Containers   1.0
    Visual Studio Tools for Containers

    Microsoft SQL Server Management Studio      14.0.17289.0
    Microsoft Analysis Services Client Tools      14.0.1016.283
    Microsoft Data Access Components (MDAC)      10.0.17134.1
    Microsoft MSXML      3.0 4.0 6.0
    Microsoft Internet Explorer      9.11.17134.0
    Microsoft .NET Framework      4.0.30319.42000
    Operating System      6.3.17134





    • Edited by Todd Gilbey Thursday, February 7, 2019 2:58 PM Added to description.
    Thursday, February 7, 2019 1:52 PM

All replies

  • Hi Todd,

    I realize that your question is more than a week old, so you may have figured it out already.

    But, if not, I was wondering if the issue might solved by setting the insert.CommandType to CommandType.StoredProcedure and removing the "EXEC" and parameters from the SqlCommand, as I've talked about in a few other replies on the topic:

    SqlCommand insert = new SqlCommand("dbo.InsertDetails", stormconn);
    insert.CommandType = System.Data.CommandType.StoredProcedure;
    insert.Parameters.AddWithValue("@Fullname", TextBox1.Text);
    insert.Parameters.AddWithValue("@Address", TextBox2.Text);
    insert.Parameters.AddWithValue("@Email", TextBox3.Text);
    cmd.ExecuteNonQuery();
    

    I don't know if that will make a difference or not, but using the CommandType like this is the preferred way to call SPs in a database.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, February 17, 2019 5:38 PM