Value "sai" cannot be null!

Question

0.00/5 (No votes)

See more:

i'm having troubles with this parameterized queries on c#. Actualy i have the error described on the title.

SAI is one of the columns which cant be null but i declare a value to 'SAI' at ("@sai", 0)

Am I doing anything wrong? Please help...

this is my code:

C#

public void SaveData()
{
    string SQL = "INSERT INTO entradas (id_veiculo,id_empresa,nome_condutor,empresa_visitante,empresa_visitar,visitado,ncartao,data,hora,obs,sector,sai) VALUES (@matricula,@idempresa,@nomecondutor,@empvisitante,@empvisitar,@visitado,@ncartao,@data,@hora,@obs,@sector, @sai)";

    using (var cn = new MySqlConnection("server=localhost;user id=root;password=12345;persistsecurityinfo=True;database=portaria;allowuservariables=True"))
    {
        cn.Open();
        using (var cmd = new MySqlCommand(SQL, cn))
        {
            if (chkhoraentrada.Checked == true && chkmatpers.Checked == true)
            {
                EmpresasCondition();
                cmd.Parameters.AddWithValue("@hora", txthoraentrada.Text);

            }
            else if (chkhoraentrada.Checked == true && chkmatpers.Checked == false)
            {
                EmpresasCondition();
                cmd.Parameters.AddWithValue("@hora", txthoraentrada.Text);

            }
            else if (chkhoraentrada.Checked == false && chkmatpers.Checked == true)
            {
                EmpresasCondition();
                cmd.Parameters.AddWithValue("@matricula", msktxtmat.Text);
            }
            else if (chkhoraentrada.Checked == false && chkmatpers.Checked == false)
            {
                EmpresasCondition();
                cmd.Parameters.AddWithValue("@matricula", txtmatricula.Text);
                cmd.Parameters.AddWithValue("@data", DateTime.Now.ToString("yyyy-MM-dd"));
                cmd.Parameters.AddWithValue("@hora", DateTime.Now.ToShortTimeString());

            }
            else
            {
                MessageBox.Show("Caso apareça este erro esporádicamente, reinicie o programa, caso aconteça pontualmente, contacte um administrador!", "Erro indeterminado", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException myex)
            {
                MessageBox.Show(myex.Message);
            }
            cn.Close();
        }
    }
}
public void EmpresasCondition()
{
    if (comboBox1.SelectedIndex == 0)
    {
        ParEmpresas();
        cmd.Parameters.AddWithValue("@empvisitar", Text = "Dura");
        cmd.Parameters.AddWithValue("@idempresa", 0);

    }
    else if (comboBox1.SelectedIndex == 1)
    {

        ParEmpresas();
        cmd.Parameters.AddWithValue("@empvisitar", Text = "Caetano Coatings");
        cmd.Parameters.AddWithValue("@idempresa", 1);
    }
    else if (comboBox1.SelectedIndex == 2)
    {
        ParEmpresas();
        cmd.Parameters.AddWithValue("@empvisitar", Text = "Lusilectra");
        cmd.Parameters.AddWithValue("@idempresa", 2);
    }
    else if (comboBox1.SelectedIndex == 3)
    {
        ParEmpresas();
        cmd.Parameters.AddWithValue("@empvisitar", Text = "Jac");
        cmd.Parameters.AddWithValue("@idempresa", 3);
    }
    else if (comboBox1.SelectedIndex == 4)
    {
        ParEmpresas();
        cmd.Parameters.AddWithValue("@empvisitar", Text = "Toyota Caetano");
        cmd.Parameters.AddWithValue("@idempresa", 4);
    }
    else if (comboBox1.SelectedIndex == 5)
    {
        ParEmpresas();
        cmd.Parameters.AddWithValue("@empvisitar", Text = "Formação");
        cmd.Parameters.AddWithValue("@idempresa", 5);
    }
    else if (comboBox1.SelectedIndex == 6)
    {
        ParEmpresas();
        cmd.Parameters.AddWithValue("@empvisitar", Text = "Visitantes");
        cmd.Parameters.AddWithValue("@idempresa", 6);
    }
    else if (comboBox1.SelectedIndex == 7)
    {
        ParEmpresas();
        cmd.Parameters.AddWithValue("@empvisitar", Text = "Parinama");
        cmd.Parameters.AddWithValue("@idempresa", 7);
    }
}
private void ParEmpresas()
{
    cmd.Parameters.AddWithValue("@nomecondutor", txtnomecondutor.Text);
    cmd.Parameters.AddWithValue("@empvisitante", txtempvis.Text);
    cmd.Parameters.AddWithValue("@visitado", txtpessoavisitar.Text);
    cmd.Parameters.AddWithValue("@ncartao", txtncartao.Text);
    cmd.Parameters.AddWithValue("@hora", DateTime.Now.ToShortTimeString());
    cmd.Parameters.AddWithValue("@obs", txtobs.Text);
    cmd.Parameters.AddWithValue("@sector", txtsector.Text);
    cmd.Parameters.AddWithValue("@sai", 0);
}

after this piece of code i have a button_click event to execute SaveData();

What I have tried:

(................................................................)

Posted 3-Mar-16 5:03am

Scribling Doodle

Updated 3-Mar-16 5:53am

v2

Add a Solution

Comments

Gautham Prabhu K 3-Mar-16 11:36am

I think you need send @sai value as string.empty instead of 0.

Scribling Doodle 3-Mar-16 11:48am

ill try it, thanks!

Scribling Doodle 3-Mar-16 11:50am

the value sai is an int, based on 0 or 1, i wont use bool because on future it might have more values, for some reason.

2 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)

OriginalGriff · Accepted Answer · 2016-03-03T05:29:00

Solution 1

No, you don't.
Not under all possible circumstances.
The only place you set @SAI is in ParEmpresas - which you only call from EmpresasCondition if your seleected index is 0,1, 2, 3, 4, 5, 6, 7. So negative values (nothing selected) or 8 or over will not set a value.
And EmpresasCondition is only ever called conditionally as well.
But...even if you fix that, ParEmpresas doesn't set the right cmd object,m because the one you use for Execute non query is inside a using block:

C#

using (var cmd = new MySqlCommand(SQL, cn))

And this masks the class level one that your method is setting!
I suspect you want to start passing the command object around to your methods instead of relying on a globally accessible variable.

Posted 3-Mar-16 5:29am

OriginalGriff

Comments

Scribling Doodle 3-Mar-16 11:35am

i set @sai at parempresas, where i use it in empresascondition, which are both connected right? So, if i execute empresascondition it will execute parempresas, which has @sai value set to 0. Any sugestion on how i can fix it?

CHill60 3-Mar-16 11:39am

See what @OriginalGriff has said about cmd - you are not adding the parameter to the "copy" of cmd that is being used in the query

Scribling Doodle 3-Mar-16 11:45am

How should i do it then? I'm trying to follow all the steps but im so confused, i can speak barely english but understanding all he said at once is a bit difficult :/ Any simplier explanation? Glad to ear from both!

Scribling Doodle 3-Mar-16 11:54am

But since i only have 7 items on my checkbox, why would i use all the circumstances if i only use that 7 items? I'm so confused...

OriginalGriff 3-Mar-16 12:12pm

The problem is that the declaration of cmd inside the method masks the version outside it:

private int i = 666;
private void MyMethod()
{
int i = 333;
...
MyOtherMethod(i);
}
private void MyOtherMethod(int x)
{
Console.WriteLine("{0}:{1}", i, x);
}

Outside MyMethod, the only available value "i" is 666.
Inside the method, the local version takes precedence and the value of "i" is 333.
So MyOtherMethod prints "666:333"

I'd suggest that you add parameters to you methods, and pass the cmd object into them for updating, and delete the class level version completely!

CHill60 3-Mar-16 12:20pm

5'd by the way :)

Scribling Doodle 4-Mar-16 3:42am

Thanks griff, you really helped me alot, adn chill too, i figured a way out, according to your methods ;) Thanks for the time given. Since i'm a newbie and you have more things to do then help, i appreciate all the effort in to this post. From the bottom of my heart, thanks!

OriginalGriff 4-Mar-16 3:52am

You're more than welcome!

Scribling Doodle 4-Mar-16 4:43am

i have another question, if i want all the values from datagrid to show on only 1 tab and have a license plate filter, to add the values to @tabempresa according to a database which contains the license plates from the companies inside this system. How hard would it be?

Scribling Doodle 4-Mar-16 4:44am

i know its too confusing, i'll just send you a PM with a fluxogram of what i'm trying to do.

OriginalGriff 4-Mar-16 5:00am

Better still, start a new question and try to explain it there - it's not related to this one! :laugh:

Scribling Doodle 4-Mar-16 5:05am

i'll do it right now. Would you mind to accept the invite on hangouts? so i can chat with you someday? ;)

OriginalGriff 4-Mar-16 5:08am

Not a lot of point - I didn't even know I had a hangouts account, much less that I actually use it! :)

Scribling Doodle 4-Mar-16 5:15am

i just went to your profile and clicked on g+ icon and it show'd me a windows to invite you to chat, just that xD :p But well, here it is the topic: http://www.codeproject.com/Questions/1083091/Fluxogram-explaining-what-im-trying-to-do

CHill60 · Accepted Answer · 2016-03-03T05:53:00

In addition to the post from @OriginalGriff above consider also the following ...

Use a switch statement rather than that long line of if-else and avoid repeating lines that are used in all cases. E.g.

C#

public void EmpresasCondition(MySqlCommand cmd)
{
    ParEmpresas(cmd);
    cmd.Parameters.AddWithValue("@idempresa", comboBox1.SelectedIndex);

    switch (comboBox1.SelectedIndex)
    {
        case 0:
            cmd.Parameters.AddWithValue("@empvisitar", Text = "Dura");
            break;
        case 1:
            cmd.Parameters.AddWithValue("@empvisitar", Text = "Caetano Coatings");
            break;
        case 2:
            cmd.Parameters.AddWithValue("@empvisitar", Text = "Lusilectra");
            break;
        case 3:
            cmd.Parameters.AddWithValue("@empvisitar", Text = "Jac");
            break;
        case 4:
            cmd.Parameters.AddWithValue("@empvisitar", Text = "Toyota Caetano");
            break;
        case 5:
            cmd.Parameters.AddWithValue("@empvisitar", Text = "Formação");
            break;
        case 6:
            cmd.Parameters.AddWithValue("@empvisitar", Text = "Visitantes");
            break;
        case 7:
            cmd.Parameters.AddWithValue("@empvisitar", Text = "Parinama");
            break;
    }
}

Note - you will need something to handle SelectedIndex = -1 (nothing selected) and any possibility of an Index > 7 being selected.
Another approach would be to have all of that text in an collection which you can reference using the SelectedIndex

C#

var textValues = new []
{
    "Dura",
    "Caetano Coatings",
    "Lusilectra",
    "Jac",
    "Toyota Caetano",
    "Formação",
    "Visitantes",
    "Parinama"
};
cmd.Parameters.AddWithValue("@empvisitar", textValues[comboBox1.SelectedIndex]);

Or, as I suspect, if that text is actually what is being displayed in the ComboBox then

C#

cmd.Parameters.AddWithValue("@empvisitar", comboBox1.SelectedValue);

would probably do. Or you might want to do some research on the differences between DisplayMember and ValueMember of a ComboBox.

Some other points:
In the example above I have demonstrated how you should be passing cmd down to your subroutines

Don't use

C#

chkhoraentrada.Checked == true 
//or
chkmatpers.Checked == false

These are Boolean values so the true / false is not needed. Use

C#

chkhoraentrada.Checked 
//and
!chkmatpers.Checked

Finally, be aware that even if you fix the problem with cmd there will be routes through your code that do not assign values to the parameters @matricula or @data