我正在构建一个简单的销售点程序,并致力于一个"搜索发票"按钮,该按钮最多允许3个搜索条件(InvoiceID,ClientName和ClientID)。这些是名为"开票"的表中 3 列的名称。
InvoiceID
是类型为 Int32
的键列,ClientName
的类型为 String
,ClientID
的类型为 Int32
。 ClientName
和ClientID
搜索完美。
我的问题:如果我在选择查询中包含InvoiceID
,则会收到以下错误。我花了几天时间试图弄清楚。
错误:Database Error: Datatype mismatch in criteria expression.
你们更有经验的程序员能帮帮我吗?谢谢!
String connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data" + @" Source=TESTDB.accdb";
String tableName = "Invoicing";
String query = String.Format("select * from [{0}] where", tableName);
//ADD IN SEARCH CRITERIA
int filled = 0;
if (invoiceBox.Text != "") { query += " InvoiceID='" + invoiceBox.Text+"'"; filled += 1; }
/*if (DateCheckBox.Checked == true)
{
if (filled>=1) { query += " and DateNTime='" + monthCalendar1.SelectionStart.ToString() + "'"; filled += 1; }
else { query += " DateNTime='" + monthCalendar1.SelectionStart.ToString()+ "'"; filled += 1; }
}
* */
if (ClientNameBox.Text != "") //Doesnot work
{
if (filled >= 1) { query += " and Client='" + ClientNameBox.Text + "'"; filled += 1; }
else { query += " Client='" + ClientNameBox.Text + "'"; filled += 1; }
}
if (ClientIDBox.Text != "") //THIS search criteria works!!!!
{
if (filled >= 1) { query += " and ClientID='" + ClientIDBox.Text + "'"; filled += 1; }
else { query += " ClientID='" + ClientIDBox.Text + "'"; filled += 1; }
}
//CHECK IF SEARCH CRITERIA ARE PRESENT
if (filled < 1) { MessageBox.Show("At least One Search criteria above is required"); return; }
DataSet dsInvoicing = new DataSet();
OleDbConnection conn = new OleDbConnection(connectionString);
try
{
//Open Database Connection
conn.Open();
OleDbDataAdapter daInvoicing = new OleDbDataAdapter(query, conn);
//Fill the DataSet
daInvoicing.Fill(dsInvoicing, tableName);
//MessageBox.Show("dsInventory has "+dsInventory.Tables[0].Rows.Count+" search results");
conn.Close();
this.dataGridView1.DataSource = dsInvoicing.Tables[0];
}
catch (OleDbException exp){ MessageBox.Show("Database Error: " + exp.Message.ToString());}
需要更多信息?如果我没有提供足够的内容,我会发布更多。数据库信息或其他。
非常感谢所有程序员。
看起来数据库中 InvoiceID 的数据类型是某种数字类型。在查询中,您将其视为字符串。尽量不要将发票 ID 值括在单引号中。