Cannot insert the value NULL into column 'UserId'

Question

I am getting this exception and can not figure out why: Cannot insert the value NULL into column 'UserId'

This is my code:

<asp:TextBox ID="FirstNameBox" runat="server" />
<br />
<br />
<asp:TextBox ID="LastNameBox" runat="server" />
<asp:SqlDataSource
    ID="InsertText"
    runat="server"
    ConnectionString="<%$ ConnectionStrings:DefaultConnection %>"
    InsertCommand="INSERT INTO UserForm (UserId,FirstName,LastName) VALUES  (@UserId,@FiName,@LaName)">

   <InsertParameters>
      <asp:ControlParameter Name="FiName" ControlID="FirstNameBox" PropertyName="Text" />
      <asp:ControlParameter Name="LaName" ControlID="LastNameBox" PropertyName="Text" />
      <asp:Parameter Name="UserId" />
   </InsertParameters>
 </asp:SqlDataSource>

This is what I have in my code behind file:

public void button1_Click(object sender, System.EventArgs e)
{
   InsertText.Insert();
}

Lagging

There must be more in your code behind file... where are you declaring the parameters you are trying to pass, for example? – Publus

The error message says it all really. The table requires than you supply a value for UserID, which you haven't. – Calculated

You set Fname and Lname, but nowhere do you seem to be setting the userID, hence the null exception. – Pantograph

easiest way to avoid this is to auto-increment a unique non-null userid field. Unless your userids are meaningful (employee #). – Elixir

Possibly because the database has a constraint on the table that a UserId is required (thus cannot be null) ...??? – Omni

I set the UserId Column to be the primary and foreign key. I set the UserId to be the GUID. When I checked the table the UserId is already there. I just want to add a users first or last name. – Lagging

@DarrellGadson - Then you don't want to INSERT, you want to UPDATE. And in that case, you need to know what the UserId of the record you want to update is, and pass that back. – Icken

@Icken - So use the UpdateCommand and I need to grab the UserId by using the MembershipUser.ProviderUserKey Property in order to identify the current user. – Lagging

Answer 1

I just grabbed the userid and performed an update command.

Guid userGuid = (Guid)Membership.GetUser(User.Identity.Name).ProviderUserKey;

UserIdLabel.Text = userGuid.ToString();

Lagging

Answer 2

Currently you have set your table in way, that you HAVE TO supply a value for UserId. If that isn't an intended behavior, I'd suggest to change the column in SQL server to auto-incremented...

It should look like this for instance:

[UserId]       INT            IDENTITY (1, 1) NOT NULL

EDIT based on comments:

I set the UserId Column to be the primary and foreign key. I set the UserId to be the GUID. When I checked the table the UserId is already there. I just want to add a users first or last name.

Not sure what's your problem then, first you're talking about INSERT, now you're talking about setting values for the existing record. If you want to update the existing record, you need to get the id first and update the values accordingly

An example SQL command may look similar to this:

UPDATE UserForm SET FirstName = @FirstName, LastName = @LastName WHERE UserId = @UserId

Insert does what it says - inserts a new record to the table.

Rooks

I tried to use an UpdateCommand. I don't get an exception but the table is not updated. – Lagging

Answer 3

you do not set any value for the userId column. if it already has been set identity. you can remove the userid when you insert the data.

Horsewhip