asked on

ODBC problem... Cannot return data from select statement!

Hello,

I'm a new vc++ dev and I'm encountering problems when trying to return data from a select statement.

Firstly I perform an insert statement which works perfectly and after I would like to execute a select query to get the primary key of the previously inserted row. (always results in SQL_NO_DATA_FOUND)

can someone help me?

                                    _tcscpy( szSQL, "insert into table1(c1, c2, c3, c4, c5, c6) VALUES ('");
                                    _tcscat(szSQL, value1);
                                    _tcscat( szSQL, "','");
                                    _tcscat( szSQL, value2);
                                    _tcscat( szSQL, "','");
                                    _tcscat( szSQL, value3);
                                    _tcscat( szSQL, "','");
                                    _tcscat( szSQL, value4);
                                    _tcscat( szSQL, "',");
                                    _tcscat( szSQL, value5);
                                    _tcscat( szSQL, ",'xxx");
                                    _tcscat( szSQL, "')" );


                                    //Execute SQL Query!
                                    Result = SQLExecDirect(hstmt, (SQLCHAR*)szSQL, SQL_NTS);



                                    //SQL Query succeeded?
                               if (Result != SQL_SUCCESS && nResult != SQL_SUCCESS_WITH_INFO)
                                          {
                                                Debug(_T("Error!"));
                                                break;
                                          }



                                    //Get Primary key of inserted row!
                                    _tcscpy(sqls, "SELECT id FROM table1 where c1= '");
                                    _tcscat(sqls, value1);
                                    _tcscat(sqls, "'");
                                    _tcscat(sqls, " and c2= '");
                                    _tcscat(sqls, value2);
                                    _tcscat(sqls, "'");
                                    _tcscat(sqls, " and c3= '");
                                    _tcscat(sqls, value3);
                                    _tcscat(sqls, "'");
                                    _tcscat(sqls, " and c4= '");
                                    _tcscat(sqls, value4);
                                    _tcscat(sqls, "'");
                                    _tcscat(sqls, " and c5= ");
                                    _tcscat(sqls, value5);
                                    _tcscat(sqls, "");
                                    _tcscat(sqls, " and c6= 'xxx'");

                                    Result = SQLExecDirect(hstmt, (SQLCHAR*)sqls, SQL_NTS);



                                    //SQL Query succeeded?
                               if (Result != SQL_SUCCESS && nResult != SQL_SUCCESS_WITH_INFO)
                                          {
                                                Debug(_T("Error!"));
                                                break;
                                          }

                                    Result = SQLFetch(hstmt);


                                    if (Result == SQL_NO_DATA_FOUND)
                                    {
                                          Debug(_T("Error : Insert failed!"));
                                          break;
                                    }


                                    TCHAR szValue[128];
                                    SDWORD cbValue;
                                    SQLGetData(hstmt, 1, SQL_C_CHAR, szValue, sizeof(szValue), &cbValue);
                                    Debug(_T("%s"), szValue);

SOLUTION

jkr

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

davyberroho

ASKER

Hi,

I've simplified the sql query and maybe I've made some mistakes by changing the column and values by simplified names.

I simply want to know which commands I've to use to return values from a select statement.

Are those commands correct:

_tcscpy(sqls, "SELECT id FROM table1 where c1= 1");
Result = SQLExecDirect(hstmt, (SQLCHAR*)sqls, SQL_NTS);
Result = SQLFetch(hstmt);
if (Result == SQL_NO_DATA_FOUND)
{
Debug(_T("No data found"));
break;
}

TCHAR szValue[128];
SDWORD cbValue;
SQLGetData(hstmt, 1, SQL_C_CHAR, szValue, sizeof(szValue), &cbValue);
Debug(_T("%s"), szValue);

ASKER CERTIFIED SOLUTION

itsmeandnobodyelse

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

itsmeandnobodyelse

Note, _tcscat is a poor method building SQL statements. Better use a string class, e. g. std::string and operator+= or streaming functions:

#include <sstream>
#include <string>
using namespace std;

...
string select = ;
ostringstream oss(select);
oss << "select id from table where c1='" << value1 << '\'' << " and c2='" << value2 << '\'';
Result = SQLExecDirect(hstmt, (SQLCHAR*)oss.c_str(), SQL_NTS);

>>>> "SELECT id FROM table1 where c1= 1";

Did you insert a column named 'id'? If 'id' isn't an automatic field the select statement wouldn't find it.

Regards, Alex

davyberroho

ASKER

I made a mistake in my select statement.

Thx guys!