价值“sai”不能为空! [英] Value "sai" cannot be null!
问题描述
我在c#上使用这个参数化查询时遇到了麻烦。 Actualy我有标题上描述的错误。
SAI是不能为空的列之一,但我在(@sai声明'SAI'的值,0)
我做错了什么?请帮忙...
这是我的代码:
public void SaveData()
{
string SQL = INSERT INTO entradas(id_veiculo,id_empresa,nome_condutor,empresa_visitante,empresa_visitar,visitado, ncartao,数据,hora,obs,sector,sai)VALUES(@ matricula,@ idempresa,@ nomecondutor,@ empvisitante,@ empvisitar,@ visitado,@ ncartao,@ data,@ hora,@ obs,@ sector,@ sai) 跨度>;
使用( var cn = new MySqlConnection( server = localhost; user id = root; password = 12345; persistsecurityinfo = True; database = portaria; allowuservariables = True))
{
cn.Open();
使用( var cmd = new MySqlCommand(SQL,cn))
{
if (chkhoraentrada.Checked == true && chkmatpers.Checked == true )
{
EmpresasCondition();
cmd.Parameters.AddWithValue( @ hora,txthoraentrada.Text);
}
其他 如果(chkhoraentrada.Checked == < span class =code-keyword> true && chkmatpers.Checked == false )
{
EmpresasCondition( );
cmd.Parameters.AddWithValue( @ hora,txthoraentrada.Text);
}
其他 如果(chkhoraentrada.Checked == < span class =code-keyword> false && chkmatpers.Checked == true )
{
EmpresasCondition( );
cmd.Parameters.AddWithValue( @ matricula,msktxtmat.Text);
}
其他 如果(chkhoraentrada.Checked == false && chkmatpers.Checked == false )
{
EmpresasCondition();
cmd.Parameters.AddWithValue( @ matricula,txtmatricula.Text);
cmd.Parameters.AddWithValue( @ data,DateTime.Now.ToString(< span class =code-string> yyyy-MM-dd));
cmd.Parameters.AddWithValue( @ hora,DateTime.Now.ToShortTimeString() );
}
else
{
MessageBox.Show( Casoapareçaesteerroesporádicamente,reinicie o programa,casoponteçacontualmente,contacte um administrador!, Erro indeterminado,MessageBoxButtons.OK,MessageBoxIcon.Error);
}
尝试
{
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 == < span class =code-digit> 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 跨度>);
}
}
私有 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 跨度>);
}
在这段代码之后我有一个button_click事件来执行SaveData();
我尝试了什么:
(....... .................................................. .......)
不,你没有。
并非在所有情况下都没有。
你设置@SAI的唯一地方是ParEmpresas
- 你只能从EmpresasCondition
调用你的seleected index是0,1,2,3,4,5,6,7。所以负值(没有选中)或8或以上不会设置值。
和EmpresasCondition
也只是有条件地调用。
但是......即使你解决了这个问题,ParEmpresas
还没有设置正确的cmd对象,因为用于执行非查询的对象位于使用块内:
使用( var cmd =new MySqlCommand(SQL,cn))
这掩盖了你的方法的类级别1正在设置!
我怀疑你想开始将命令对象传递给你的方法,而不是依赖于一个全局可访问的变量。
除了帖子从@OriginalGriff上面考虑以下......
使用switch语句而不是if-else $ c的那一长行$ 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 = 雅克跨度>);
break ;
case 4 :
cmd.Parameters.AddWithValue( @ empvisitar,Text = 丰田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 ;
}
}注意 - 你需要一些东西来处理SelectedIndex = -1(没有选择)和任何索引的可能性> 7被选中。
另一种方法是将所有文本都放在一个集合中,你可以使用SelectedIndex引用
var textValues = new []
{
Dura,
Caetano Coatings,
Lusilectra,
Jac,
Toyota Caetano,
Formação ,
Visitantes,
Parinama
};
cmd.Parameters.AddWithValue( @ empvisitar,textValues [comboBox1.SelectedIndex]) ;或者,正如我怀疑的那样,如果该文本实际上是在ComboBox中显示的内容,那么
cmd.Parameters.AddWithValue( @ empvisitar,comboBox1.SelectedValue);
可能会这样做。或者您可能想要对ComboBox的DisplayMember和ValueMember之间的差异进行一些研究。
其他一些要点:
在示例中上面我已经演示了如何将cmd
传递给你的子程序
不要使用chkhoraentrada.Checked == true
// 或
chkmatpers.Checked == false
这些是布尔值,因此不需要true / false。使用chkhoraentrada.Checked
// 和
!chkmatpers.Checked
最后,请注意,即使您使用cmd解决问题
您的代码中会有路由没有为参数赋值@matricula
或@data
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:
(................................................................)
No, you don't.
Not under all possible circumstances.
The only place you set @SAI is inParEmpresas
- which you only call fromEmpresasCondition
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.
AndEmpresasCondition
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.
In addition to the post from @OriginalGriff above consider also the following ...
Use a switch statement rather than that long line ofif-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 passingcmd
down to your subroutines
Don't usechkhoraentrada.Checked == true //or chkmatpers.Checked == false
These are Boolean values so the true / false is not needed. Usechkhoraentrada.Checked //and !chkmatpers.Checked
Finally, be aware that even if you fix the problem withcmd
there will be routes through your code that do not assign values to the parameters@matricula
or@data
这篇关于价值“sai”不能为空!的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!