Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Scrollable cursor problem

Posted on 2006-03-22
13
Medium Priority
?
387 Views
Last Modified: 2012-08-14
Hey experts!

I was wondering if somebody could help me here.

I’m creating a GUI administrator win32 application for a fantasy Formula 1 game (currently being developed by me). The problem is that, when I retrieve the data from the database, scrollable cursor for next is working fine (reading through the data) but I cannot get it to work for previous and I don't know why.

Here is the code
#include "CDataConn.h"

CDataConn::CDataConn() //Allocate environment, connection and
{ //statement handles
SQLAllocEnv (&henv);
SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
SQLAllocConnect(henv, &hdbc);
SQLAllocStmt(hdbc,&hstmt);

rc = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_SCROLLABLE, (SQLPOINTER)SQL_SCROLLABLE, -6 );
}

CDataConn::~CDataConn() //Free all handles
{
SQLFreeEnv(&henv);
SQLFreeConnect(&hdbc);
SQLFreeStmt(&hstmt, NULL);
}

bool CDataConn::connect(const char* userNameIn, const char* passwordIn, const char* dsnIn) //connect to database
{ //using details passed in
rc = SQLConnect(hdbc, (SQLCHAR*)dsnIn, SQL_NTS, (SQLCHAR*)userNameIn,
SQL_NTS, (SQLCHAR*)passwordIn, SQL_NTS);

if(rc == SQL_ERROR) // return false if could
{ //not connect
return false;
}

return true;
}


HSTMT CDataConn::exeStmt(const char* statement)
{
SQLAllocStmt(hdbc,&hstmt);

SQLExecDirect(hstmt, (SQLCHAR*)statement, SQL_NTS);

return hstmt;
}

HDBC CDataConn::getHdbc()
{
return hdbc;
}

Thanks in advance.
0
Comment
Question by:DanBAtkinson
  • 9
  • 4
13 Comments
 
LVL 86

Accepted Solution

by:
jkr earned 2000 total points
ID: 16262655
How are you using 'SQLFetchScroll()'? It supports

SQL_FETCH_NEXT
SQL_FETCH_PRIOR
SQL_FETCH_FIRST
SQL_FETCH_LAST
SQL_FETCH_ABSOLUTE
SQL_FETCH_RELATIVE
SQL_FETCH_BOOKMARK


BTW, I'd rather recommend to use ODBC classes rather than the plain API to do that. See e.g. http://www.codeproject.com/database/#ODBC
0
 

Author Comment

by:DanBAtkinson
ID: 16262767
Can I ask why you recommend you'd use ODBC classes rather than the API?
0
 

Author Comment

by:DanBAtkinson
ID: 16262789
////////////////////////////////////////////////USERS DIALOG IMPLEMENTATION//////////////////////////////////

CUserDialog::CUserDialog()
{
}

void CUserDialog::init(HINSTANCE hinstance, HWND hwnd, CDataConn *conn)
{
dlgHwnd = CreateDialog(hinstance,MAKEINTRESOURCE(IDD_USER_DETAILS),hwnd,(DLGPROC)DialogProc);
clearHstmt();

if(rc != SQL_ERROR)
hstmt = conn->exeStmt("SELECT * FROM USERS");

bindCols();

next(); //sets all fields to first row's values
}



void CUserDialog::next()
{
if((rc = SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0)) != SQL_NO_DATA && userId != 0)
{
setFields();
}
}

void CUserDialog::previous()
{
if((rc = SQLFetchScroll(hstmt, SQL_FETCH_PRIOR, 0)) != SQL_NO_DATA && userId != 0)
{
setFields();
}
}

void CUserDialog::first()
{
if((rc = SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 0)) != SQL_NO_DATA && userId != 0)
{
setFields();
}
}

void CUserDialog::last()
{
if((rc = SQLFetchScroll(hstmt, SQL_FETCH_LAST, 0)) != SQL_NO_DATA && userId != 0)
{
setFields();
}
}

void CUserDialog::deleteEntry()
{
int result = MessageBox(dlgHwnd, (LPCSTR)"Are you sure you wish to delete this record?",
(LPCSTR)"Delete Record?", MB_OKCANCEL);

if(result != IDCANCEL)
{
int id = GetDlgItemInt(dlgHwnd, IDC_ID, 0, false);

char sql [] = "DELETE FROM USERS WHERE USERS_ID = ";
char numberStr[3];

itoa(id, numberStr, 10);

strcat(sql, numberStr);

hstmt = g_Connection.exeStmt(sql);

hstmt = g_Connection.exeStmt("SELECT * FROM USERS");
bindCols();
next();
}
}

void CUserDialog::bindCols()
{
SQLBindCol(hstmt, 1, SQL_INTEGER, &userId, 0, &userIdInd);
SQLBindCol(hstmt, 2, SQL_CHAR, &userName, sizeof(userName), &userNameInd);
SQLBindCol(hstmt, 3, SQL_CHAR, &password, sizeof(password), &passwordInd);
SQLBindCol(hstmt, 4, SQL_CHAR, &email, sizeof(email), &emailInd);
SQLBindCol(hstmt, 5, SQL_INTEGER, &teamId, 0, &teamIdInd);
SQLBindCol(hstmt, 6, SQL_CHAR, &teamName, sizeof(teamName), &teamNameInd);
SQLBindCol(hstmt, 7, SQL_INTEGER, &driver1, 0, &driver1Ind);
SQLBindCol(hstmt, 8, SQL_INTEGER, &driver2, 0, &driver2Ind);
SQLBindCol(hstmt, 9, SQL_INTEGER, &engine, 0, &engineInd);
SQLBindCol(hstmt, 10, SQL_INTEGER, &constructor, 0, &constructorInd);
SQLBindCol(hstmt, 11, SQL_INTEGER, &points, 0, &pointsInd);
}

void CUserDialog::setFields()
{
SetDlgItemInt(dlgHwnd, IDC_ID, userId, true);
SetDlgItemText(dlgHwnd, IDC_UNAME, (LPCTSTR)userName);
SetDlgItemText(dlgHwnd, IDC_PASS, (LPCTSTR)password);
SetDlgItemText(dlgHwnd, IDC_EMAIL, (LPCTSTR)email);
SetDlgItemInt(dlgHwnd, IDC_TID, teamId, true);
SetDlgItemText(dlgHwnd, IDC_TEAMNAME, (LPCTSTR)teamName);
SetDlgItemInt(dlgHwnd, IDC_DRIVER1, driver1, true);
SetDlgItemInt(dlgHwnd, IDC_DRIVER2, driver2, true);
SetDlgItemInt(dlgHwnd, IDC_ENGINE, engine, true);
SetDlgItemInt(dlgHwnd, IDC_CONSTRUCTOR, constructor, true);
SetDlgItemInt(dlgHwnd, IDC_POINTS, points, true);
}

void CUserDialog::clearHstmt()
{
hstmt = NULL;
}
////////////////////////////////////////////////USERS DIALOG IMPLEMENTATION//////////////////////////////////
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:DanBAtkinson
ID: 16262937
As you can see the following are being used:

SQL_FETCH_NEXT
SQL_FETCH_PRIOR
SQL_FETCH_FIRST
SQL_FETCH_LAST

void CUserDialog::next()
{
  if((rc = SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0)) != SQL_NO_DATA && userId != 0)
  {
    setFields();
  }
}

void CUserDialog::previous()
{
  if((rc = SQLFetchScroll(hstmt, SQL_FETCH_PRIOR, 0)) != SQL_NO_DATA && userId != 0)
  {
    setFields();
  }
}

void CUserDialog::first()
{
  if((rc = SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 0)) != SQL_NO_DATA && userId != 0)
  {
    setFields();
  }
}

void CUserDialog::last()
{
  if((rc = SQLFetchScroll(hstmt, SQL_FETCH_LAST, 0)) != SQL_NO_DATA && userId != 0)
  {
    setFields();
  }
}
0
 
LVL 86

Expert Comment

by:jkr
ID: 16262942
>>Can I ask why you recommend you'd use ODBC classes rather than the API?

Sure, they're way easier to handle and you don't have to reinvent the wheel.
0
 
LVL 86

Expert Comment

by:jkr
ID: 16262979
What are the error codes you get?
0
 

Author Comment

by:DanBAtkinson
ID: 16263133
It compiles perfectly no errors at all but once the application is running it doesn’t work.
I believe the 0 in this line of code:

if((rc = SQLFetchScroll(hstmt, SQL_FETCH_PRIOR, 0))

is causing the problem, I tried replacing it with SQL_IS_INTEGER (something like that found in MSDN) and it says its not a valid identifier
0
 

Author Comment

by:DanBAtkinson
ID: 16263854
Is there anything you can think of at all which may be the problem?
0
 

Author Comment

by:DanBAtkinson
ID: 16273475
Does anyone have any solution for this at all?

I really would like an answer as soon as possible!

Cheers.
0
 
LVL 86

Expert Comment

by:jkr
ID: 16273618
>>if((rc = SQLFetchScroll(hstmt, SQL_FETCH_PRIOR, 0))
>>is causing the problem

That was clear. What is 'rc' when it fails? That's what  meant regarding the error code.
0
 

Author Comment

by:DanBAtkinson
ID: 16275660
rc is nothing. Nothing gets set.
0
 

Author Comment

by:DanBAtkinson
ID: 16280994
Never mind...

I've kind of figured out a solution where I kept a static count of the rows, and to access previous I decrement the count by one and execute the statement again (Something like that anyway don’t have the code to explain it).

If anyone has a more elegant solution using the code above, I'd like to hear it, please.
0
 

Author Comment

by:DanBAtkinson
ID: 16309940
Going back through the code, it seems that one of the SQL_FETCH's was wrong.

Thanks.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: SunnyDark
This article's goal is to present you with an easy to use XML wrapper for C++ and also present some interesting techniques that you might use with MS C++. The reason I built this class is to ease the pain of using XML files with C++, since there is…
This article shows you how to optimize memory allocations in C++ using placement new. Applicable especially to usecases dealing with creation of large number of objects. A brief on problem: Lets take example problem for simplicity: - I have a G…
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question