Query parameterized cannot insert value with c# and SQL Server

Question

I have a parameterized query and it was working fine, but when I delete de DB and create it again, with the same values and everything, it throws an exception that says it cannot insert value NULL with the value sexo, but all the values are assigned, here it's the code:

    try{
      var cmdPersona_Log = new SqlCommand();
      cmdPersona_Log.Parameters.Clear();
      cmdPersona_Log.Connection = mySqlConnection;
      cmdPersona_Log.CommandType = CommandType.Text;
      cmdPersona_Log.CommandText = @"INSERT INTO [Tomin].[TominRH].[Persona_Log] "
       + "([Id_Action],[Id_User],[Id_Date],[Id_Entidad],[Nombre],[Paterno],[Materno],[Sexo],[Id_Nacionalidad])"
       + " Values (1, 'Admin', @fecha, @id_entidad, @nombre, @paterno, @materno, @sexo, 52)";
      cmdPersona_Log.Parameters.AddWithValue("@fecha", DateTime.Now);
      cmdPersona_Log.Parameters.AddWithValue("@id_entidad", dbRow["CUENTA"].ToString().Trim());
      cmdPersona_Log.Parameters.AddWithValue("@nombre", nombre ?? string.Empty);
      cmdPersona_Log.Parameters.AddWithValue("@paterno", paterno ?? string.Empty);
      cmdPersona_Log.Parameters.AddWithValue("@materno", materno ?? string.Empty);
      cmdPersona_Log.Parameters.AddWithValue("@sexo", 1);
      cmdPersona_Log.ExecuteNonQuery();
   }
   catch(Exception e) 
   {
      MessageBox.Show(dbRow["CUENTA"] + " Persona_log  " + e.ToString());
   }

I've checked the DB and it doesn't seem to be the problem, any sugestion??

string, but the problem is with sexo, I forgot to say that... — poz2k4444
– poz2k4444, Commented Aug 31, 2012 at 21:23
Exactly the same problem, the data type is bit not boolean, I don't know if it's the same, but I think it's something with the parameterized values... — poz2k4444
– poz2k4444, Commented Aug 31, 2012 at 22:03
There is no Boolean type in SQL. A bit in SQL is translated to bool in .NET and vice-versa. — D Stanley
– D Stanley, Commented Aug 31, 2012 at 22:07

D Stanley · Accepted Answer · 2012-08-31 22:05:39Z

2

You may be running into a case where AddWithValue isn't inferring your parameter type of bit properly. Use true/false instead of 1/0:

cmdPersona_Log.Parameters.AddWithValue("@sexo", true);

answered Aug 31, 2012 at 22:05

D Stanley

153k12 gold badges189 silver badges257 bronze badges

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

5 Comments

poz2k4444 Over a year ago

Yes, that I cannot insert NULL value

LarsTech Over a year ago

@poz2k4444 And if you get rid of the parameter and change the sql to use 1 instead of the sexo parameter, what happens?

poz2k4444 Over a year ago

The same exception, indeed I changed to parameterized query because I had what you say

poz2k4444 Over a year ago

I don't know why, but there is no problem anymore, I left the project a few days and suddenly everything works just fine :S, any way, thanks for the help...

Yoztastic Over a year ago

+1 having spoken to the original author, this recommendation, as we both expected fixed the problem. see comments attached to my answer. I suspect (although it is unproven) that the code changes were not being built between runs.

O.Charyshkin · Accepted Answer · 2012-08-31 21:13:46Z

0

Make sure that all fields of the table which are supposed to be not null are listed in the INSERT-clause

answered Aug 31, 2012 at 21:13

O.Charyshkin

335 bronze badges

3 Comments

poz2k4444 Over a year ago

CHecked... The table has 10 parameters and 9 cannot be NULL and those are the parameters that I send...

O.Charyshkin Over a year ago

@poz2k4444 Can you you give sql-expression which creates the table?

poz2k4444 Over a year ago

I don't know why, but there is no problem anymore, I left the project a few days and suddenly everything works just fine :S, any way, thanks for the help...

Yoztastic · Accepted Answer · 2012-08-31 22:13:25Z

0

Have you tried using this form cmd.Parameters.Add("@SomeID", SqlDbType.Int, 4).Value = where you can be explicit about the SqlDbType?

answered Aug 31, 2012 at 22:13

Yoztastic

8642 gold badges9 silver badges21 bronze badges

5 Comments

poz2k4444 Over a year ago

I don't know why, but there is no problem anymore, I left the project a few days and suddenly everything works just fine :S, any way, thanks for the help...

Yoztastic Over a year ago

I have had this problem in the past where the project was not really running the re-built changes made to your code so effectively re-running the broken code even once you had fixed it. This can sometime lead to breaking the code even more with edits apparently not leading to any change in behaviour. Just out of interest is your working code exactly the same as your original post? I believe a few of us thought cmdPersona_Log.Parameters.AddWithValue("@sexo", 1); would not work.

poz2k4444 Over a year ago

MY code is working with cmdPersona_Log.Parameters.AddWithValue("@sexo", true); that was the last change I made and then I left the project for a few days... How could I know when the code is running with the changes I made??

Yoztastic Over a year ago

Rebuild All. Also if you suspect your latest changes are not being used deliberately break it by throwing and exception in code that has to be executed. If no exception is thrown your code changes are not being used. Both @user:1081897 and myself suspected that you needed to change cmdPersona_Log.Parameters.AddWithValue("@sexo", 1); to cmdPersona_Log.Parameters.AddWithValue("@sexo", true); and have commented as such. It does appear from your response that it dis not work. I think it is worth editing and Up-voting D Stanley, to help future viewers of this question.

poz2k4444 Over a year ago

I did change cmdPersona_Log.Parameters.AddWithValue("@sexo", 1); to cmdPersona_Log.Parameters.AddWithValue("@sexo", true); This is the last change I did, and when it throws an exception, It always throwed the exception mentioned, and suddenly it doens't, I don't know why but the code is working perfect by now, anyway I appreciate your help guys, thanks a lot!

Collectives™ on Stack Overflow

Query parameterized cannot insert value with c# and SQL Server

3 Answers 3

5 Comments

3 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related