Null Problem during the update of a mysql database

Question

0.00/5 (No votes)

See more:

I am trying to update a mysql table through C# mysql client but my Mysql server is throwing following error:

MySql.Error: column Emp_Name Can't be null

I dont know where I am commiting a mistake...below is my query source code:

commandLine = @"update employee_details set Emp_Name = @Emp_Name ,Emp_Mname = @Emp_Mname , Emp_Lname= @Emp_Lname , Emp_DOB= @Emp_DOB , Emp_Permanent_Addr = @Emp_Permanent_Addr, Emp_Present_Addr = @Emp_Present_Addr, Emp_ContactNo= @Emp_ContactNo,
       Emp_emailId = @Emp_emailId,Emp_Sex = @Emp_Sex,Emp_Mar_Stat = @Emp_Mar_Stat,Emp_Nationality = @Emp_Nationality,Emp_Highest_Education = @Emp_Highest_Education,Emp_FOS = @Emp_FOS,Emp_Last_Course_SD = @Emp_Last_Course_SD,Emp_Last_Course_ED = @Emp_Last_Course_ED,Emp_Last_Inst_Name=@Emp_last_Inst_Name,
       Emp_Last_Inst_Loc=@Emp_Last_Inst_Loc,Emp_Educational_Sumary=@Emp_Educational_Sumary,Emp_Pan_No=@Emp_Pan_No,Emp_TIN_No=@Emp_TIN_No,Emp_Passport_No=@Emp_Passport_No,Emp_PHIL_No=@Emp_PHIL_No,Emp_PAG=@Emp_PAG,Emp_Last_Job_Title=@Emp_Last_Job_Title,
       Emp_Last_Com_Name=@Emp_Last_Com_Name,Emp_Last_Industry_Type=@Emp_Last_Industry_Type,Emp_Last_Job_Start_Date=@Emp_Last_Job_Start_Date,Emp_Last_Job_End_Date=@Emp_Last_Job_End_Date,Emp_Last_Job_Role=@Emp_Last_Job_Role,Emp_Last_Industry_Location=@Emp_Last_Industry_Location;";
 cmd.CommandText = commandLine;
 MessageBox.Show(tbFname.Text);
 cmd.Parameters.AddWithValue("@Emp_Name", tbFname.Text);
 cmd.Parameters.AddWithValue("@Emp_Mname", tbMName.Text);
 cmd.Parameters.AddWithValue("@Emp_Lname", tbLName.Text);
 cmd.Parameters.AddWithValue("@Emp_DOB", dateTimePickerDOB.Value.ToString("yyyy-MM-dd"));
 
// cmd.Parameters.AddWithValue("@Emp_DOJ", dat);//today's date will be date of joining
 cmd.Parameters.AddWithValue("@Emp_Permanent_Addr", tbPermanentAddr.Text + tbPermanentPin.Text);
 cmd.Parameters.AddWithValue("@Emp_Present_Addr", tbPresentAddr.Text + tbPresentPin.Text);
 cmd.Parameters.AddWithValue("@Emp_ContactNo", tbContactNo.Text);
 cmd.Parameters.AddWithValue("@Emp_emailId", tbEmail.Text);
// cmd.Parameters.AddWithValue("@Emp_Type", employeeType.ToString());
 cmd.Parameters.AddWithValue("@Emp_Sex", cbGender.SelectedItem.ToString());
 cmd.Parameters.AddWithValue("@Emp_Mar_Stat", cbCivilStatus.SelectedItem.ToString());
 cmd.Parameters.AddWithValue("@Emp_Nationality", tbNationality.Text);
 
 cmd.Parameters.AddWithValue("@Emp_Highest_Education", cbEducationLevel.SelectedItem.ToString());
 cmd.Parameters.AddWithValue("@Emp_FOS", tbFOS.Text);
 cmd.Parameters.AddWithValue("@Emp_Last_Course_SD", dateTimePickerEducationSD.Value.ToString("yyyy-MM-dd"));
 cmd.Parameters.AddWithValue("@Emp_Last_Course_ED", dateTimePickerEducationED.Value.ToString("yyyy-MM-dd"));
 cmd.Parameters.AddWithValue("@Emp_last_Inst_Name", tbInstitutionname.Text);
 cmd.Parameters.AddWithValue("@Emp_Last_Inst_Loc", tbInstLoc.Text);
 cmd.Parameters.AddWithValue("@Emp_Educational_Sumary", tbInstSummary.Text);
 
 cmd.Parameters.AddWithValue("@Emp_Pan_No", tbPAN.Text);
 cmd.Parameters.AddWithValue("@Emp_TIN_No", tbTIN.Text);
 cmd.Parameters.AddWithValue("@Emp_Passport_No", tbPassport.Text);
 cmd.Parameters.AddWithValue("@Emp_PHIL_No", tbPhil.Text);
 cmd.Parameters.AddWithValue("@Emp_PAG", tbPag.Text);
 
 cmd.Parameters.AddWithValue("@Emp_Last_Job_Title", tbJobTitle.Text);
 cmd.Parameters.AddWithValue("@Emp_Last_Com_Name", tbCompName.Text);
 cmd.Parameters.AddWithValue("@Emp_Last_Industry_Type", cbIndustryType.SelectedItem.ToString());
 cmd.Parameters.AddWithValue("@Emp_last_Job_Start_Date", dateTimePickerJobSD.Value.ToString("yyyy-MM-dd"));
 cmd.Parameters.AddWithValue("@Emp_last_Job_End_Date", dateTimePickerJobED.Value.ToString("yyyy-MM-dd"));
 cmd.Parameters.AddWithValue("@Emp_Last_Job_Role", tbJobRole.Text);
 cmd.Parameters.AddWithValue("@Emp_Last_Industry_Location", tbIndustryLoc.Text);

--------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
Can any one plz tell me where am I wrong in this.....And yes Emp_Name is a not null column...but the value I am passing from tbFname is not a null but a proper string....

Thanks in advance...

Posted 28-Jan-11 10:47am

nautiyal.sudhanshu

Updated 31-Jan-11 8:46am

JOAT-MON

v3

Add a Solution

Comments

Sergey Alexandrovich Kryukov 28-Jan-11 17:12pm

I really like English usage example of "committing a mistake". Even though correct usage is "making a mistake", for _some_ mistakes "committing a mistake" expresses a nature of certain kind of mistakes more precisely. :-)
--SA

William Winner 28-Jan-11 18:17pm

Honestly, the only thing that I would say is that the value has to be null.

Have you added a Try/Catch block on the ExectureNonQuery or whatever you're using? And if so, are you including in the exception message what the value of tbFname.Text is when the error is thrown?

JOAT-MON 28-Jan-11 18:53pm

The error is apparently not obvious, given what we know about your setup. A couple questions to help us identify what is happening (any additional information will be helpful):
1. Does the employee_details table already have rows in it? There is no 'WHERE' clause to specify which employee's details you are trying to update, are you trying to update all rows in the table with the same information?
2. Is it possible you want to 'INSERT' this information, rather than 'UPDATE' it?
3. What version of the MySql client are you using?
4. How is your command setup? What is the CommandType? Are you using ParameterCheck?

nautiyal.sudhanshu 29-Jan-11 0:27am

@SA: :)
ok some clarifications:

1. NO the value of tbFname is not null...I even tried cmd.Parameters.AddWithValue("@Emp_Name", "Fred"); but it saya the same thing
2. where clause is there...actually one part of query I missed..that is

.... where Employee_Id="+emp_Id.toString()+";";

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Hybridron · Answer 1 · 2011-01-31T09:57:00

Just try to update Emp_name column only and step through your code to see what happens. Check to see if a value is actaully getting passed in.

You can also copy the sql generated by the update and try to run that in the database directly to see if an error occurs.

If it still throws and exception, make the a column nullable to see if the update is successful.

Let us know how you go.

Hope this helps

Ronnie

Henry Minute · Answer 2 · 2011-01-28T12:26:00

Firstly, I cannot see anything wrong with the code you have posted.

I have never used the MySQL Client but are you sure that it can deal with parameterized queries? (It would be really odd if it didn't, but still)

Try replacing your first parameter assignment with:

C#

cmd.Parameters.AddWithValue("@Emp_Name", "Fred");

does it still throw the error? Or does the error move down to @Emp_MName, or a different error.

The idea being to try to tie it down to either, the value really is null (as William says) or something odd is happening with the way it deals with parameters.

JOAT-MON · Answer 3 · 2011-01-31T07:41:00

Try using a ? instead of @. Some versions of the client have issues with the @ character.

C#

commandLine = @"update employee_details set Emp_Name = ?Emp_Name ,Emp_Mname = ?Emp_Mname , Emp_Lname= ?Emp_Lname , ...";

cmd.Parameters.AddWithValue("?Emp_Name", tbFname.Text);
cmd.Parameters.AddWithValue("?Emp_Mname", tbMName.Text);
cmd.Parameters.AddWithValue("?Emp_Lname", tbLName.Text);

Null Problem during the update of a mysql database

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0