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
– John Saunders

2014-01-09 20:28:30 +00:00
Commented Jan 9, 2014 at 20:28 — John Saunders
– John Saunders, Commented Jan 9, 2014 at 20:28

Dylan Brams · Accepted Answer · 2014-01-09 20:58:51Z

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 9, 2014 at 20:58

Dylan Brams

2,1091 gold badge20 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

psycho Over a year ago

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.

Dylan Brams Over a year ago

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.

John Saunders · Accepted Answer · 2014-01-09 20:24:46Z

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 9, 2014 at 20:24

John Saunders

162k26 gold badges252 silver badges403 bronze badges

2 Comments

psycho Over a year ago

Apologies I had a matching number I was editing trying to fix the problem. I have edited the errors in the question.

John Saunders Over a year ago

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.

Collectives™ on Stack Overflow

ASP.NET Database error

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related