I'm using Visual Studio 2010, Win 7, Access 2010, 32 bit. The first SQL statement executes the following four don't.
The code...Sometimes the insert works, sometimes no. The TOTAL and QUOTA return 0 as well.
#include <windows.h>
#include <stdio.h>
#include <sqlext.h>
const char* DAM = "Direct ODBC";
SQLCHAR szDSN[256] =
"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=C:\\FILEBLOCK\\Fileblocker.accdb;";
main()
{
HENV hEnv, hEnv2, hEnv3, hEnv4, hEnv5;
HDBC hDbc, hDbc2, hDbc3, hDbc4, hDbc5;
SQLRETURN rc, NEW, TOTAL, QUOTA, UP;
SQLSMALLINT iConnStrLength2Ptr;
SQLCHAR szConnStrOut[255];
SQLCHAR* query = (SQLCHAR*)"SELECT tblIP.[IPAddress], tblIP.[IPType], tblIP.[IPStatus], tblIP.[IPMax] FROM tblIP WHERE tblIP.[IPAddress]='173.201.216.2' AND tblIP.[IPType]=3 AND tblIP.[IPStatus]=1 AND tblIP.[IPMax]=0;";
SQLCHAR* query2 = (SQLCHAR*)"INSERT INTO tblDownloads (tblDownloads.[DownloadIP] , tblDownloads.[DownloadCount]) VALUES('173.201.216.2', 1)";
SQLCHAR* query3 = (SQLCHAR*)"SELECT SUM(DownloadCount) AS DT FROM tblDownloads WHERE tblDownloads.[DownloadIP]='173.201.216.2'";
SQLCHAR* query4 = (SQLCHAR*)"SELECT tblIP.[IPQuota] FROM tblIP WHERE tblIP.[IPAddress] = '173.201.216.2'";
SQLCHAR* query5 = (SQLCHAR*)"UPDATE tblIP SET tblIP.[IPMax] WHERE tblIP.[IPAddress] = '173.201.216.2'";
SQLINTEGER rowCount = 0;
SQLSMALLINT fieldCount = 0;
HSTMT hStmt, hStmt2, hStmt3, hStmt4, hStmt5;
rc = SQLAllocEnv(&hEnv);
NEW = SQLAllocEnv(&hEnv2);
TOTAL = SQLAllocEnv(&hEnv3);
QUOTA = SQLAllocEnv(&hEnv4);
UP = SQLAllocEnv(&hEnv5);
rc = SQLAllocConnect(hEnv, &hDbc);
NEW = SQLAllocConnect(hEnv3, &hDbc2);
TOTAL = SQLAllocConnect(hEnv3, &hDbc3);
QUOTA = SQLAllocConnect(hEnv4, &hDbc4);
UP = SQLAllocConnect(hEnv5, &hDbc5);
rc = SQLDriverConnect(hDbc, NULL, szDSN, _countof(szDSN),
szConnStrOut, 255, &iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
if (SQL_SUCCEEDED(rc))
{
printf("%s: Successfully connected to database. Data source name: \n %s\n",
DAM, szConnStrOut);
rc = SQLAllocStmt(hDbc,&hStmt);
rc = SQLPrepare(hStmt, query, SQL_NTS);
rc = SQLExecute(hStmt);
rc = SQLFetch(hStmt);
while (SQL_SUCCEEDED(rc))
{
rc = SQLFetch(hStmt);
rowCount++;
};
printf("%s: Total Legit IP Count: %d\n", DAM, rowCount);
rc = SQLFreeStmt(hStmt, SQL_DROP);
if (rowCount >= 1)
{
printf("PASS\n");
NEW = SQLDriverConnect(hDbc2, NULL, szDSN, _countof(szDSN), szConnStrOut, 255, &iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
NEW = SQLAllocStmt(hDbc2,&hStmt2);
NEW = SQLPrepare(hStmt2, query2, SQL_NTS);
NEW = SQLExecute(hStmt2);
NEW = SQLFreeStmt(hStmt2, SQL_DROP);
TOTAL = SQLDriverConnect(hDbc3, NULL, szDSN, _countof(szDSN), szConnStrOut, 255, &iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
TOTAL = SQLAllocStmt(hDbc3,&hStmt3);
TOTAL = SQLPrepare(hStmt3, query3, SQL_NTS);
TOTAL = SQLExecute(hStmt3);
TOTAL = SQLFetch(hStmt3);
printf("%s: Total of Downloads: %d\n", DAM, TOTAL);
TOTAL = SQLFreeStmt(hStmt3, SQL_DROP);
QUOTA = SQLDriverConnect(hDbc4, NULL, szDSN, _countof(szDSN), szConnStrOut, 255, &iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
QUOTA = SQLAllocStmt(hDbc4,&hStmt4);
QUOTA = SQLPrepare(hStmt4, query4, SQL_NTS);
QUOTA = SQLExecute(hStmt4);
QUOTA = SQLFetch(hStmt4);
printf("%s: Quota For IP Address: %d\n", DAM, QUOTA);
QUOTA = SQLFreeStmt(hStmt4, SQL_DROP);
if (TOTAL >= QUOTA)
{
UP = SQLDriverConnect(hDbc5, NULL, szDSN, _countof(szDSN), szConnStrOut, 255, &iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
UP = SQLAllocStmt(hDbc5,&hStmt5);
UP = SQLPrepare(hStmt5, query5, SQL_NTS);
UP = SQLExecute(hStmt5);
UP = SQLFetch(hStmt5);
UP = SQLFreeStmt(hStmt5, SQL_DROP);
}
else if (rowCount == 0)
{
printf("FAIL\n");
rc = SQLFreeStmt(hStmt, SQL_DROP);
}
system("pause");
}
else
{
printf("%s: Couldn't connect to %s.\n", DAM, szDSN);
}
SQLDisconnect(hDbc);
SQLDisconnect(hDbc2);
SQLDisconnect(hDbc3);
SQLDisconnect(hDbc4);
SQLDisconnect(hDbc5);
SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
SQLFreeHandle(SQL_HANDLE_DBC, hDbc2);
SQLFreeHandle(SQL_HANDLE_DBC, hDbc3);
SQLFreeHandle(SQL_HANDLE_DBC, hDbc4);
SQLFreeHandle(SQL_HANDLE_DBC, hDbc5);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv2);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv3);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv4);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv5);
}
}</sqlext.h></stdio.h></windows.h>