• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

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);


0
davyberroho
Asked:
davyberroho
  • 2
  • 2
2 Solutions
 
jkrCommented:
Doea that compile at all? I mean, you have statemnts like

_tcscat(sqls, " and c3= '");

which could be ' " or " '

It the 1st one applies, you should check your SQL statement for syntactiacal correctness, since there might be too many unmatched closing single quotes around. Just try

MessageBox(NULL,sqls,"SWL statement",MB_OK);

to see what it exactly is before executing.
0
 
davyberrohoAuthor Commented:
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);
0
 
itsmeandnobodyelseCommented:
You inserted values without apostrophes but in the select statement there are apostrophes?

What data type were the columns? Could you post both (fully built) insert statement and select statement?

>>>> SQLExecDirect(hstmt

Don't know whether you could/should reuse statement handles. I always allocated new statement handles for any new statement while freeing the old ones.

>>>> where c1= 1

if the columns were of type SQL_CHAR or SQL_VARCHAR you need to quote the values. You should avoid to add spaces before or after the equal character

   where c1='1'

Regards, Alex

0
 
itsmeandnobodyelseCommented:
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
0
 
davyberrohoAuthor Commented:
I made a mistake in my select statement.


Thx guys!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now