none
ADO.NET MSAccess C++ SQL ERROR RRS feed

  • Question

  • Good Day To All:

     

    I think the error I'm receiving has been quite popular in this field however all the solutions I tried are still failing.  The issue should be pretty simple but it has had me stuck this entire week so any of your help will be greatly appreciated.

     

    I'm receiving this error: "Data type mismatch in criteria expression"

     

    Here's the code that I know is specifically responsible for this error:

     

    CODE

     

    double sixh = 16000;

    // Query the database

    cmProducts->CommandText = "SELECT RiskID, RiskFOM FROM Risk WHERE PositionID IN (SELECT PositionID FROM [Position] WHERE Up =  ' sixh ' )";

     

    CODE

     

    The error is clearly: Up = 'sixh' and i have tried replacing that segment with

     

    Up = '+ sixh +'

    Up = '& sixh &'

    Up = ['sixh']

    Up =  sixh 

     

    All to no avail Sad  It however works perfectly at Up = 16000, the data type in the MS Access field name is also number.

     

    Thanks for your help and suggestions!

     

    Edit:

     

    I've also used tried the code found on this page:

    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/fbbeea55-e8c8-445f-a3a3-4e11d6d3e6a4/

     

    They've resulted in these errors:

     

    error C2146: syntax error : missing ';' before identifier 'sixh'

    error C2111: '+' : pointer addition requires integral operand

    error C2296: '&' : illegal, left operand has type 'const char [101]'

    error C2297: '&' : illegal, right operand has type 'double'

     

    Friday, September 12, 2008 3:52 PM

Answers

  • For All those wondering I have found the solution!!!! Thank God!  And Thank you VMazur for your input!

     

    So, looking at the concatenation method I utilized in C++ I realized that although I was concatenating the string it was not being EQUALED to the CommandText, upon doing so I achieved victory!  Here's the award winning code:

     

    CODE

     

    cmProducts->CommandText = cmProducts->CommandText->Concat(S"SELECT RiskID, RiskFOM FROM Risk WHERE PositionID IN (SELECT PositionID FROM [Position] WHERE Up = ", sixh.ToString(), S")");

     

    /CODE

     

    Cheers Me Mateys!

     

     

    Monday, September 15, 2008 9:32 PM

All replies

  • SQL statement has no idea about variable name you declared in your code. You need to pass variable value as a parameter to your query or (which is not preferable) to concatenate value to your SQL string. I am not C++ guy, but if it support + as concatenation then your code would look like

     

     

    double sixh = 16000;

    // Query the database

    cmProducts->CommandText = "SELECT RiskID, RiskFOM FROM Risk WHERE PositionID IN (SELECT PositionID FROM [Position] WHERE Up =  " + sixh + ")";

    Friday, September 12, 2008 11:40 PM
    Moderator
  • Thank you for your reply, VMazur

     

    I have indeed tried to concatenate the value using the C++ programming language and unfortunately it produces a different type of error.  Here is the sample code:

     

    CODE

     

    cmProducts->CommandText->Concat(S"SELECT RiskID, RiskFOM FROM Risk WHERE PositionID IN (SELECT PositionID FROM [Position] WHERE Up = ");

    cmProducts->CommandText->Concat(sixh.ToString());

    cmProducts->CommandText->Concat(S")");

     

    /CODE

     

    It now says:

     

    An unhandled exception of type 'System.IndexOutOfRangeException' occurred in system.data.dll

    Additional information: Index was outside the bounds of the array.

     

    And it stops at the commented line found here:

     

    CODE

     

    Console::WriteLine(S"\n------------------------------------");

    while (reader->Read())

    {

    Console::Write(reader->GetValue(0));

    Console::Write(S", ");

    //Console::WriteLine(reader->GetValue(1));

    }

    Console::WriteLine(S"--------------------------------------");

     

    /CODE

     

    The data it outputs is also incorrect, it only shows the last row in the database!

     

    I have also attempted using the parameter method, as in:

     

    CODE

     

    cmProducts->CommandText = "SELECT RiskID, RiskFOM FROM Risk WHERE PositionID IN (SELECT PositionID FROM [Position] WHERE Up = @sixh)";

    cmProducts->Parameters->Add("@sixh", OleDb::Integer)->Value = sixh;

     

     /CODE

     

    I made sure to add:

    using namespace System:: Data:: OleDb;

    at the top before main and yet it gives me an extremely preposterous error:

     

    ConnectedApplication.cpp(58) : error C2039: 'Integer' : is not a member of 'System:: Data:: OleDb'

    ConnectedApplication.cpp(58) : error C2065: 'Integer' : undeclared identifier

    ConnectedApplication.cpp(58) : error C2227: left of '->Value' must point to class/struct/union

     

    And this is utterly ridiculous as the "integer" member is pulled directly from the function definition!

     

    Please help!

     

     

    Monday, September 15, 2008 1:34 PM
  • For All those wondering I have found the solution!!!! Thank God!  And Thank you VMazur for your input!

     

    So, looking at the concatenation method I utilized in C++ I realized that although I was concatenating the string it was not being EQUALED to the CommandText, upon doing so I achieved victory!  Here's the award winning code:

     

    CODE

     

    cmProducts->CommandText = cmProducts->CommandText->Concat(S"SELECT RiskID, RiskFOM FROM Risk WHERE PositionID IN (SELECT PositionID FROM [Position] WHERE Up = ", sixh.ToString(), S")");

     

    /CODE

     

    Cheers Me Mateys!

     

     

    Monday, September 15, 2008 9:32 PM