Click here to Skip to main content
14,176,750 members
Rate this:
 
Please Sign up or sign in to vote.
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:

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
Updated 3-Mar-16 5:53am
v2
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.
Rate this: bad
 
good
Please Sign up or sign in to vote.

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:
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.
   
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
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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.
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
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
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
chkhoraentrada.Checked == true 
//or
chkmatpers.Checked == false

These are Boolean values so the true / false is not needed. Use
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
   
Comments
Scribling Doodle 3-Mar-16 12:00pm
   
you're a f***ing genius! Thank you so much for this explanation, i understood all with this simple steps, one thing i couldnt understand was this last paragraph where you say that will be routes through my code that cant assign values to the parameters @matricula or @data...
CHill60 3-Mar-16 12:10pm
   
If chkhoraentrada is Checked you never get to the code that sets values for @matricula or @data because of the way you have used if-else.
Scribling Doodle 3-Mar-16 12:09pm
   
Not it doesnt show me any error, but most of the data that i insert gets nulled on the database... This is the code i have, and after its an image of my database table.
Code: http://pastebin.com/076fxRhC

Database: https://gyazo.com/dbf8c3907ebf7b1d72c3abd568db8331 (It is missing 2 columns, @nomecondutor and @sector, but both have null value)
CHill60 3-Mar-16 12:19pm
   
You should be passing cmd down to ParEmpresas() as well! There was no point in moving the assignment to @sai
Scribling Doodle 3-Mar-16 12:22pm
   
http://pastebin.com/57CebMJJ Fixed it ;) now it works as it should be :p
CHill60 3-Mar-16 19:15pm
   
Good. Feel free to formally accept both the solutions to close off the post
Scribling Doodle 4-Mar-16 3:40am
   
i haven't accepted it yet so more people could see this topic, as it could help someone with the same problem ;) But i'll accept it right away. Thanks for the time given
CHill60 4-Mar-16 6:16am
   
My pleasure.
Don't worry about "close off the post" - it will still be here for anyone who hits the same problem. In fact you might even get more solutions at a later date! By "close off" I really just meant that it is marked as resolved - which is also helpful for anyone else who comes along ... at least they know the solutions work :)
Scribling Doodle 4-Mar-16 6:20am
   
off-topic: would you mind to check my new question? i'm having troubles in other circumstances :p Hope you can help me ;)

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 | Cookies | Terms of Service
Web01 | 2.8.190525.1 | Last Updated 3 Mar 2016
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

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