ASP.NET Database error

Question

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

Note that this question has nothing to do with ASP.NET. – John Saunders Jan 09 '14 at 20:28 — John Saunders, Jan 09 '14 at 20:28

score 1 · Answer 1 · answered Jan 09 '14 at 20:24

1

You have five columns, but only 3 values.

Your statement is

INSERT INTO ContactLog (Name, Email, Subject, Message, DateSent)
                VALUES (@0, @1, @3)

What happened to @2 and @4?

Maybe you need

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

answered Jan 09 '14 at 20:24

John Saunders

160,644
26
247
397

Apologies I had a matching number I was editing trying to fix the problem. I have edited the errors in the question. – psycho Jan 09 '14 at 20:42
As @Dylan: says, you need the `ID` column to be an `IDENTITY` column, or to have some other default value, since it cannot have nulls. – John Saunders Jan 09 '14 at 21:01

score 1 · Accepted Answer · answered Jan 09 '14 at 20:58

1

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.

answered Jan 09 '14 at 20:58

Dylan Brams

2,089
1
19
36

Thank you very much @Dylan B and John Saunders for your replies that was exactly the problem. I'm not too familiar with the ins and outs of databases. I presume the Identity attribute is something like auto increment. – psycho Jan 09 '14 at 22:05
Yeah, autoincrement is the Access version of the same thing. I'm not actually sure which is canonical, but they're functionally equivalent and (my assumption is) implemented a lot of different ways. – Dylan Brams Jan 09 '14 at 22:06

ASP.NET Database error

2 Answers2