Error in SQL statement when using variable

[gm]

Please help if you can. I get an error trying to use a variable in a SQL
statement. This is for an Access database using C# and I get the following
error:
System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.

Here is my code:

01 private void buttonTransfer_Click(object sender, EventArgs e)
02 {
03 decimal idSearch = Convert.ToInt32(this.textBox1.Text);
04 string strConn, strSQL;
05 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
06 "Data Source=m:\\myFiles\\ProjFiles.mdb;" +
07 "User id=admin;" +
08 "Password=";
09 strSQL = "SELECT * FROM Checking WHERE Deposit = '" + idSearch
+ "'";
10 DataTable tbl = new DataTable();
11 OleDbConnection cn = new OleDbConnection(strConn);
12 OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
13 cn.Open();
14 da.Fill(tbl);
15
16 foreach (DataRow dr in tbl.Rows)
17 {
18 this.IDTextBox.Text = dr["ID"].ToString();
19 this.DepositTextBox.Text = dr["Deposit"].ToString();
20 this.DepositDateTextBox.Text = dr["DepositDate"].ToString();
21 this.DepositMemoTextBox.Text = dr["DepositMemo"].ToString();
22 this.VerifyTextBox.Text = dr["Verify"].ToString();
23 }
24
25 cn.Close();
26 }

This error occurs at Line 14 but I feel it is because the syntax in my
statement in Line 09 is incorrect.

--
Thanks! gm

[Erland Sommarskog]

gm (mrtec...@yahoo.com) writes:
> Please help if you can. I get an error trying to use a variable in a SQL
> statement. This is for an Access database using C# and I get the
> following error:
> System.Data.OleDb.OleDbException: Data type mismatch in criteria
> expression.
>
> Here is my code:
>
> 01 private void buttonTransfer_Click(object sender, EventArgs e)
> 02 {
> 03 decimal idSearch = Convert.ToInt32(this.textBox1.Text);
> 04 string strConn, strSQL;
> 05 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
> 06 "Data Source=m:\\myFiles\\ProjFiles.mdb;" +
> 07 "User id=admin;" +
> 08 "Password=";
> 09 strSQL = "SELECT * FROM Checking WHERE Deposit = '" + idSearch + "'";
> 10 DataTable tbl = new DataTable();
> 11 OleDbConnection cn = new OleDbConnection(strConn);
> 12 OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
> 13 cn.Open();
> 14 da.Fill(tbl);
>
>
>

> This error occurs at Line 14 but I feel it is because the syntax in my
> statement in Line 09 is incorrect.

Of course the error is in line 14, because that's where the SQL statement
is executed. C# does not care two wits about the syntax of your SQL
statement; that is only a string as far as it's concerned.

As for the actual error, you will need to have in Access newsgroup
for details, but if I am to guess, Deposit is a numeric column and
you are comparing it to a string which is not permitted.

In any case, apart from the data-type mismatch, there is another problem
with you code. It should read:

strSQL = "SELECT * FROM Checking WHERE Deposit = ?";
OleDbCommand cmd = new OleDbCommand(strSQL);
cmd.Parameters.Add(OleDbType.<datatype>, idSearch);

Then you pass the Command object to the DataAdapter. You will need to
look up the exact details for the Parameters.Add method, as I am only
an occasional .Net programmer.

Why you should do this? Because this protects from unpleasanties if
idSearch would include a single quote.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[gm]

Thanks for the help.
There are some other obvious things wrong with it too but I guess I was too
close to it to recognize them.