Scrollable cursor problem

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.
DanBAtkinsonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jkrCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DanBAtkinsonAuthor Commented:
Can I ask why you recommend you'd use ODBC classes rather than the API?
0
DanBAtkinsonAuthor Commented:
////////////////////////////////////////////////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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

DanBAtkinsonAuthor Commented:
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
jkrCommented:
>>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
jkrCommented:
What are the error codes you get?
0
DanBAtkinsonAuthor Commented:
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
DanBAtkinsonAuthor Commented:
Is there anything you can think of at all which may be the problem?
0
DanBAtkinsonAuthor Commented:
Does anyone have any solution for this at all?

I really would like an answer as soon as possible!

Cheers.
0
jkrCommented:
>>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
DanBAtkinsonAuthor Commented:
rc is nothing. Nothing gets set.
0
DanBAtkinsonAuthor Commented:
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
DanBAtkinsonAuthor Commented:
Going back through the code, it seems that one of the SQL_FETCH's was wrong.

Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C++

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.