Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET SQL update
I have a ListView and was trying to edit the comments made by users and after which, it will be updated in my database. However, i encounter this error when i select the update button in my listView. Where have i gone wrong?
 
Error Message:
Cannot insert the value NULL into column 'Title', table. 
Column does not allow nulls. UPDATE fails.
The statement has been terminated.
 
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
 
My .cs code:
MembershipUser currentUser = Membership.GetUser();
        Guid currentUserId = (Guid)currentUser.ProviderUserKey;
        int i = e.ItemIndex;
        TextBox tb = ListView1.Items[i].FindControl("commentContentTextBox") as TextBox;
        string test = tb.Text;
        int CommentID = Convert.ToInt16(ListView1.DataKeys[i].Value);
 
        string connectionString = ConfigurationManager.ConnectionStrings["ASPNETDBConnectionString"].ConnectionString;
        SqlConnection myConnect = new SqlConnection(connectionString);
 
        string updateSQL = "UPDATE GenBook set Title=@Title, comment=@comment, package=@package, UserId=@UserId WHERE GenBookID = @GenBookID ";
 
        using (SqlConnection myConnection = new SqlConnection(connectionString))
        {
            myConnection.Open();
 
            SqlCommand myCommand = new SqlCommand(updateSQL, myConnection);
            myCommand.Parameters.AddWithValue("@GenBookID", GenBookID);
            myCommand.Parameters.AddWithValue("@Title", TextBox1.Text.Trim());
            myCommand.Parameters.AddWithValue("@comment", TextBox2.Text.Trim());
            myCommand.Parameters.AddWithValue("@UserId", currentUserId);
            myCommand.Parameters.AddWithValue("@package", package.Text.Trim());
 
            myCommand.ExecuteNonQuery();
            myConnection.Close();
            ListView1.EditIndex = -1;
            ListView1.DataBind();
}
Posted 1-Aug-12 4:35am
kellycx745
Comments
Kenneth Haugland at 1-Aug-12 9:38am
   
Do you have allow null's in the SQL server?
kellycx at 1-Aug-12 9:59am
   
No, only my package is allowed to be null. The rest i disallow them to be null.
Wes Aday at 1-Aug-12 9:39am
   
Does Textbox1 actually contain a value?
kellycx at 1-Aug-12 10:00am
   
For editing of comments, I only restrict the user to edit their own content of the comments. As for the title, they are not allowed to make changes.
Wes Aday at 1-Aug-12 10:04am
   
I do not think you understand. When you debugged your code, stepping through your code using a debugger, when you are getting the error, does Textbox1 have a value? Seems to me that that is where you should be looking since that is where the error is coming from.
kellycx at 1-Aug-12 10:12am
   
Sorry to misunderstand. I get what you mean now. I have debugged my codes and TextBox1 in fact does have a value. They are able to capture the value in TextBox1.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Your textbOx1 dont have any text inside it. Its blank.
 
Make sure it is a required field. Else in table, set the column property to allow null.
  Permalink  
Comments
kellycx at 1-Aug-12 10:03am
   
But the TextBox1 itself already have a value. As i validated at the top before they post their comment, the Title and Content of the comments have to have some text. Therefore, even if they are not allowed to edit the title after they have posted their comment, the TextBox1 will still contain the original Text that they have entered beforehand. Isn't it?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Cannot insert the value NULL into column 'Title', table.
Column does not allow nulls. UPDATE fails.
It's self explanatory. You are trying to insert a NULL in a column that is set not to allow null values.
 
Either put a default value or set column to allow NULL value.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

I think you have gotten all the information you need. You should step through the debugger, see for instance this article: 10+ powerful debugging tricks with Visual Studio[^] or this one http://msdn.microsoft.com/en-us/library/aa290350%28v=VS.71%29.aspx[^]
 
Either as everybody has said:
TextBox1 is empty, as you probarbly would find out using the debugger.
And you have either goit to change your code to include a check for this, or
Allow nulls in the SQL database
  Permalink  
Comments
kellycx at 1-Aug-12 10:42am
   
I have a new error which states
 
Cannot insert the value NULL into column 'UserId', table
 
My UserId's data type is uniqueidentifier. It is encrypted and therefore, i do not really know how not to make it not null.
Kenneth Haugland at 1-Aug-12 10:50am
   
If you replace this line:
myCommand.Parameters.AddWithValue("@Title", TextBox1.Text.Trim());
with this:
myCommand.Parameters.AddWithValue("@Title", "MyHeader");
Does it work then?
kellycx at 1-Aug-12 11:10am
   
I managed to write off my previous error but now comes a new error that says:
Cannot insert the value NULL into column 'UserId', table
Matt T Heffron at 1-Aug-12 16:59pm
   
Have you verified that
Guid currentUserId = (Guid)currentUser.ProviderUserKey;
has actually set a non-null value into currentUserId?
kellycx at 3-Aug-12 8:29am
   
How am I suppose to do that?
Matt T Heffron at 3-Aug-12 13:18pm
   
Basic Debugging!
Set a breakpoint there and look at the value.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 7,130
1 DamithSL 5,089
2 Maciej Los 4,866
3 Sergey Alexandrovich Kryukov 4,647
4 Kornfeld Eliyahu Peter 4,409


Advertise | Privacy | Mobile
Web02 | 2.8.141223.1 | Last Updated 1 Aug 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100