在条件表达式错误中获取数据类型不匹配 [英] Getting data type mismatch in criteria expression error
问题描述
下面是我的示例代码
i我在以下代码中收到错误
1）con1。打开（）;
cmd.ExecuteNonQuery（）;
2）da.Fill（ds）;
i我正在使用访问数据库。
textbox4& textbox5是我的访问数据库中的双精度数据类型，即我必须输入hrs表示数值数据
我尝试过：
private void button1_Click（object sender，EventArgs e）
{
using（OleDbConnection con1 = new OleDbConnection（con））
{
OleDbCommand cmd = new OleDbCommand（）;
cmd.Connection = con1;
OleDbDataAdapter da;
DataSet ds = new DataSet（）;
if（（textBox4.Text == string。空）||（textBox5.Text == string.Empty）||（comboBox1.Text == string.Empty）||（comboBox2.Text == string.Empty））
{
MessageBox.Show（请在相关字段中输入您的所有详细信息...... !!!!!，消息，MessageBoxButtons.OK，MessageBoxIcon.Warning ）;
}
其他
{
cmd =新OleDbCommand（SELECT * from Spldetails where Date ='+ dateTimePicker1.Text +'和EmpId ='+ textBox1.Text +'和Splwrk ='+ comboBox2.Text +'和Splwrkdetails ='+ textBox6.Text +'，con1） ;
con1.Open（）;
da = new OleDbDataAdapter（cmd）;
da.Fill（ds）;
int i = ds.Tables [0] .Rows.Count;
if（i> 0）
{
MessageBox.Show（重复详情!!!!您已经在+ dateTimePicker1.Text +上提交了您的详细信息。）;
//ds.Clear（）;
textBox4.Text =;
textBox5.Text =;
textBox6.Text =;
comboBox1.Text =;
comboBox2.Text =;
}
其他
{
试试
{
string command =INSERT INTO Spldetails（[Date]，[EmpId]，[EmpName]，[Designation]，[Records ]，[Splwrkhrs]，[团队]，[Splwrk]，[Splwrkdetails]，[Allottedby]）VALUES（'+ this.dat eTimePicker1.Text +'，'+ this.textBox1.Text +'，'+ this.textBox2.Text +'，'+ this.textBox3.Text +'，'+ this.textBox4。文字+'，'+ this.textBox5.Text +'，'+ this.textBox8.Text +'，'+ this.comboBox2.Text +'，'+ this.textBox6.Text + '，'+ this.comboBox1.Text +'）;
cmd = new OleDbCommand（command，con1）;
con1.Open（）;
cmd.ExecuteNonQuery（）;
MessageBox.Show（祝贺....你的详细信息已成功提交）;
textBox4.Text =;
textBox5.Text =;
textBox6.Text =;
comboBox1.Text =;
comboBox2.Text =;
dataGridV iew1.Refresh（）;
}
catch（Exception exp）
{
MessageBox.Show（error+ exp）;
}
}
}
}
}
below is my sample code
i am getting error at below codes
1)con1.Open();
cmd.ExecuteNonQuery();
2)da.Fill(ds);
i am using access database.
textbox4 & textbox5 are doubles datatype in my access datbase i.e i have to enter only hrs means numerical data
What I have tried:
private void button1_Click(object sender, EventArgs e)
{
using (OleDbConnection con1 = new OleDbConnection(con))
{
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con1;
OleDbDataAdapter da;
DataSet ds = new DataSet();
if ((textBox4.Text == string.Empty) || (textBox5.Text == string.Empty) || (comboBox1.Text == string.Empty) || (comboBox2.Text == string.Empty))
{
MessageBox.Show("Please enter Your All the Details in the Related Fields ...!!!!!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
cmd = new OleDbCommand("SELECT * from Spldetails where Date = '" + dateTimePicker1.Text + "' and EmpId = '" + textBox1.Text + "' and Splwrk = '" + comboBox2.Text + "' and Splwrkdetails = '" + textBox6.Text + "'", con1);
con1.Open();
da = new OleDbDataAdapter(cmd);
da.Fill(ds);
int i = ds.Tables[0].Rows.Count;
if (i > 0)
{
MessageBox.Show("Duplicate Details !!!! You have already Submitted your details on " + dateTimePicker1.Text + ".");
//ds.Clear();
textBox4.Text = "";
textBox5.Text = "";
textBox6.Text = "";
comboBox1.Text = "";
comboBox2.Text = "";
}
else
{
try
{
string command = "INSERT INTO Spldetails([Date],[EmpId],[EmpName],[Designation],[Records],[Splwrkhrs],[Team],[Splwrk],[Splwrkdetails],[Allottedby]) VALUES('" + this.dateTimePicker1.Text + "','" + this.textBox1.Text + "','" + this.textBox2.Text + "','" + this.textBox3.Text + "','" + this.textBox4.Text + "','" + this.textBox5.Text + "','" + this.textBox8.Text + "','" + this.comboBox2.Text + "','" + this.textBox6.Text + "','" + this.comboBox1.Text + "')";
cmd = new OleDbCommand(command, con1);
con1.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Congratulations....Your Details have been submitted successfully");
textBox4.Text = "";
textBox5.Text = "";
textBox6.Text = "";
comboBox1.Text = "";
comboBox2.Text = "";
dataGridView1.Refresh();
}
catch (Exception exp)
{
MessageBox.Show("error" + exp);
}
}
}
}
}
推荐答案
string command = "INSERT INTO Spldetails([Date],[EmpId],[EmpName],[Designation],[Records],[Splwrkhrs],[Team],[Splwrk],[Splwrkdetails],[Allottedby]) VALUES('" + this.dateTimePicker1.Text + "','" + this.textBox1.Text + "','" + this.textBox2.Text + "','" + this.textBox3.Text + "','" + this.textBox4.Text + "','" + this.textBox5.Text + "','" + this.textBox8.Text + "','" + this.comboBox2.Text + "','" + this.textBox6.Text + "','" + this.comboBox1.Text + "')";
不是你问题的解决方案，而是你遇到的另一个问题。
永远不要通过连接字符串来构建SQL查询。迟早，您将使用用户输入来执行此操作，这会打开一个名为SQL注入的漏洞，这对您的数据库很容易并且容易出错。
名称中的单引号你的程序崩溃。如果用户输入像Brian O'Conner这样的名称可能会使您的应用程序崩溃，那么这是一个SQL注入漏洞，崩溃是最少的问题，恶意用户输入，并且它被提升为具有所有凭据的SQL命令。
SQL注入 - 维基百科 [ ^ ]
SQL注入 [ ^ ]
按示例进行SQL注入攻击 [ ^ ]
PHP：SQL注入 - 手册 [ ^ ]
SQL注入预防备忘单 - OWASP [ ^ ]
Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
您需要在select语句中使用参数，而不是在一起使用concat字符串。这将允许您在where子句中指定值的类型。例如：
You need to use parameters in your select statement, not concat strings together. This will allow you you specify the types of the values in your where clause. for example:
string sqlStr = "SELECT [MenuID],[MenuText],[URL]"
+ " FROM [dbo].[ServiceDeskMenu]"
+ " WHERE [ParentID] = @parent_type"
+ " ORDER by[Position] ";
SqlParameter param = new SqlParameter
{
ParameterName = "@parent_type",
DbType = DbType.Int32,
Value = parentType
};
然后你可以将param对象添加到你的sql命令对象并执行你的查询
then you can add the param object to you sql command object and execute your query
sqlCommand cmd = new SqlCommand(sqlStr, conn);
if(param!=null)
{
cmd.Parameters.Add(param);
}
还有一件事，不要选择*。只需选择您想要的字段。
One more thing, don't select *. Just select the fields you want.
这篇关于在条件表达式错误中获取数据类型不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
