Search code examples
sqldatabasenullsql-insertparameterized-query

ASP.NET Database error

I am trying to follow a tutorial online and I have followed the steps exactly however i keep getting an error when trying to write to a database.

Here Is the code for a simple form

@{
var name = string.Empty;
var email = string.Empty;
var subject = string.Empty;
var message = string.Empty;

if (IsPost){
    name = Request["txtName"];
    email = Request["txtEmail"];
    subject = Request["txtSubject"];
    message = Request["txtMessage"];

    var db = Database.Open("FormAndDataDB");

    db.Execute("INSERT INTO ContactLog (Name, Email, Subject, Message, DateSent) " +
      "VALUES (@0, @1, @2, @3, @4)", name, email, subject, message, DateTime.Now);
}    

 }

 <doctype html>

 <html lang="en">
<head>
    <meta charset="utf-8" />
    <title></title>
</head>

<body>
    <form action="contact.cshtml" method="POST">
        <p>
            <label for="txtName">Name:</label><br />
            <input type="text" name="txtName" id="txtName" value="@name"/>
        </p>

        <p>
            <label for="txtEmail">Email:</label><br />
            <input type="text" name="txtEmail" id="txtEmail" value="@email" />
        </p>

        <p>
            <label for="txtSubject">Subject:</label><br />
            <input type="text" name="txtSubject" id="txtSubject" value="@subject" />
        </p>

        <p>
            <label for="txtMessage">Message:</label><br />
            <textarea name="txtMessage" id="txtMessage" cols="40" rows="10" >@message</textarea>
        </p>

        <p>
            <input type="submit" value="send" />
        </p>
    </form>
</body>
</html>

Here is the error i get in visual studio on attempt of execution of the SQL insert statement:

"An exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll but was not handled in user code"

This is the error text returned from SQL and displayed on the webpage:

"The column cannot contain null values. [ Column name = ID,Table name = ContactLog ]"

I have filled out the form completely

Solution

  • Your errors in the question notwithstanding, the problem seems to be that you've got a table (contactLog) with a column (id) that has the NOT NULL attribute attached to it. You are trying to insert a row without a value in said column, and that is causing this error.

    To fix this either insert a value into the [ID] column or set the identity attribute to TRUE instead of FALSE on said column and allow it to autogenerate / autoincrement.

    My assumption is that even when you fixed the syntax of the insert statement you still couldn't process the insert properly.