Casting from ExecuteScalar on OleDbCommand in C#

Question

I am receiving the following error from my code:

The following errors occurred:

System.Data.OleDb.OleDbException (0x80040E07): Data type mismatch in criteria expression. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteScalar() at NJDataExtractExceltoAccess.Form1.FindAcctId(String x, OleDbConnection y) in C:\Path\Form1.cs:line 158

private int FindAcctId(string x, OleDbConnection y)
{
    //try
    //{
        string[] ID = x.Split('-');

        string lookup = "Select Id from tbl_name where Col1 = '110' And Col2 = " + ID[0] + "And Col3 = " + ID[1] + "And Col4 = " + ID[2] + "And Col5 = " + ID[3] + "And Col6 = " + ID[4] + "And Col7 = " + ID[5] + "And Col7 = " + ID[6];
        OleDbCommand cmd = new OleDbCommand(lookup, y);
        Int32 result = (Int32)cmd.ExecuteScalar();  // line 158
        Return_val = (int)result;
    //}
    //catch (Exception Er)
    //{
    //    OutputBox.Text = "The following errors occurred: " + Er;
    //}
    return Return_val;
}

The Microsoft page says this:

A typical ExecuteScalar query can be formatted as in the following C# example:

cmd.CommandText = "SELECT COUNT(*) FROM dbo.region";
Int32 count = (Int32) cmd.ExecuteScalar();

I have also tried:

int result = (int)cmd.ExecuteScalar();

and

object result = cmd.ExecuteScalar();

but I receive the same datatype error at the same line. How do I cast from ExecuteScalar() on an OleDbCommand object (the connection and insert string work)?

Besides the fact you should be using parameterized queries (http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters.aspx), are all of your columns numeric? If they are varchar, you need single quotes around each. — sgeddes, Jun 01 '13 at 01:28
You are missing spaces at the beginning of each "And ColXXX = " string segment, so you'll end up with a query that's likely improperly formatted, i.e. "And Col2 = 1And Col3 = 2And Col4 = ..." etc — Kirill Shlenskiy, Jun 01 '13 at 01:29

Igor · Answer 1 · 2013-06-04T01:46:14.413

2

The error has nothing to do with the cast of ExecuteScalar result. It is error in your sql where clause:

You have to check the types of the fields used in where condition. Values for string fields (varchar) should be wrapped in single quotes, values for numeric columns - should not.
You miss spaces between values of fields and AND connecting to the next condition.

edited Jun 04 '13 at 01:46

answered Jun 01 '13 at 01:29

Igor

15,833
1
27
32

Casting from ExecuteScalar on OleDbCommand in C#

1 Answers1