Click here to Skip to main content
13,400,584 members (33,008 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
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
Updated 21-Mar-17 22:30pm
Comments
Kenneth Haugland 1-Aug-12 9:38am
   
Do you have allow null's in the SQL server?
kellycx 1-Aug-12 9:59am
   
No, only my package is allowed to be null. The rest i disallow them to be null.
Wes Aday 1-Aug-12 9:39am
   
Does Textbox1 actually contain a value?
kellycx 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 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 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 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 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 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 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 1-Aug-12 16:59pm
   
Have you verified that
Guid currentUserId = (Guid)currentUser.ProviderUserKey;
has actually set a non-null value into currentUserId?
kellycx 3-Aug-12 8:29am
   
How am I suppose to do that?
Matt T Heffron 3-Aug-12 13:18pm
   
Basic Debugging!
Set a breakpoint there and look at the value.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

protected void btnlogin_Click(object sender, EventArgs e)
   {
       erroruserpage.Text = "";

       cn.ConnectionString = ConfigurationManager.ConnectionStrings["bs"].ToString();
       cn.Open();

       SqlCommand cmtest = new SqlCommand("insert into enquiryform (EmailAddr,last_login_time)values(@EmailAddr,@last_login_time)", cn);
       cmtest.Parameters.AddWithValue("@EmailAddr", txtusername.Text);
       cmtest.Parameters.AddWithValue("@last_login_time", DateTime.Today.ToString());

                cmtest.ExecuteNonQuery();
        cn.Close();

       SqlCommand cmtest1 = new SqlCommand("insert into tbl_admincreation_bs(tur_emailid,last_login_time)values(@emailid,@time)",cn);
       cmtest1.Parameters.AddWithValue("@emailid", txtusername.Text);
       cmtest1.Parameters.AddWithValue("@last_login_time", DateTime.Today.ToString());
       cmtest1.ExecuteNonQuery();
       cn.Close();


       cmd1.ExecuteNonQuery();
  Permalink  
v2
Comments
Member 13045840 22-Mar-17 3:36am
   
error in the cmtest.Exeutenonquery
Cannot insert the value NULL into column 'contactno', table 'projectname.dbo.table'; column does not allow nulls. INSERT fails. how to clear the error in asp.net
ppolymorphe 22-Mar-17 16:06pm
   
Open a new question with your problem.
You will never get here because it is an answer for another question.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web03 | 2.8.180218.2 | Last Updated 22 Mar 2017
Copyright © CodeProject, 1999-2018
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