Link to home
Create AccountLog in
Avatar of DanBAtkinson
DanBAtkinson

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of jkr
jkr
Flag of Germany image
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of DanBAtkinson
DanBAtkinson

ASKER

Can I ask why you recommend you'd use ODBC classes rather than the API?
Avatar of DanBAtkinson
DanBAtkinson

ASKER

////////////////////////////////////////////////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//////////////////////////////////
Avatar of DanBAtkinson
DanBAtkinson

ASKER

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();
  }
}
Avatar of jkr
jkr
Flag of Germany image
>>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.
Avatar of jkr
jkr
Flag of Germany image
What are the error codes you get?
Avatar of DanBAtkinson
DanBAtkinson

ASKER

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
Avatar of DanBAtkinson
DanBAtkinson

ASKER

Is there anything you can think of at all which may be the problem?
Avatar of DanBAtkinson
DanBAtkinson

ASKER

Does anyone have any solution for this at all?

I really would like an answer as soon as possible!

Cheers.
Avatar of jkr
jkr
Flag of Germany image
>>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.
Avatar of DanBAtkinson
DanBAtkinson

ASKER

rc is nothing. Nothing gets set.
Avatar of DanBAtkinson
DanBAtkinson

ASKER

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.
Avatar of DanBAtkinson
DanBAtkinson

ASKER

Going back through the code, it seems that one of the SQL_FETCH's was wrong.

Thanks.