Click here to Skip to main content
15,936,975 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.

C++
//Burton
#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'";
	
	/* Number of rows and columns in result set */
    SQLINTEGER      rowCount = 0;
    SQLSMALLINT     fieldCount = 0;
    HSTMT           hStmt, hStmt2, hStmt3, hStmt4, hStmt5;

    /* Allocate an environment handle */
    rc = SQLAllocEnv(&hEnv);
	NEW = SQLAllocEnv(&hEnv2);
	TOTAL = SQLAllocEnv(&hEnv3);
	QUOTA = SQLAllocEnv(&hEnv4);
	UP = SQLAllocEnv(&hEnv5);
    /* Allocate a connection handle */
    rc = SQLAllocConnect(hEnv, &hDbc);
	NEW = SQLAllocConnect(hEnv3, &hDbc2);
	TOTAL = SQLAllocConnect(hEnv3, &hDbc3);
	QUOTA = SQLAllocConnect(hEnv4, &hDbc4);
    UP = SQLAllocConnect(hEnv5, &hDbc5);
	
	/* Connect to the 'Fileblocker.accdb' database */
    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);

        /* Prepare SQL query */
        rc = SQLAllocStmt(hDbc,&hStmt);
        rc = SQLPrepare(hStmt, query, SQL_NTS);
       
       	/* Execute the query and create a record set */
        rc = SQLExecute(hStmt); 
        
		/* Loop through the rows in the result set */
            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);
    }

    /* Disconnect and free up allocated handles */
    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>
Posted
Updated 19-Oct-11 13:48pm
v5

Step through your code using the debugger. Your if (SQL_SUCCEEDED(rc)) calls can hide issues that occur, but are a good idea to keep there for the final release as a failsafe. You'll probably find that there is a call there that fails and you're just not trapping the error correctly.
 
Share this answer
 
Comments
Member 7766180 19-Oct-11 16:03pm    
I think it has something to do with hStmt, I need to use it more than once, so I think I need to release it and reibstitute it.
Member 7766180 19-Oct-11 16:28pm    
OK, got the first of three to work...
SQLExecute("INSERT INTO tblDownloads (tblDownloads.[DownloadIP] , tblDownloads.[DownloadCount]) VALUES('173.201.216.2', 1);"); Onward!!!
Member 7766180 19-Oct-11 16:34pm    
This comes up with undeclared identifier....
int TOTAL;
TOTAL = SQLFetch ("SELECT tblDownloads.[DownloadCount] WHERE tblDownloads.[DownloadIP] = '173.201.216.2';");
int QUOTA;
QUOTA = SQLFetch ("SELECT tblIP.[IPQuota], WHERE tblIPID.[IPAddress] = '173.201.216.2';");
if (TOTAL >= QUOTA)
{
SQLExecute ("UPDATE tblIP SET tblIP.[IPMax] WHERE tblIP.[IPAddress] = '173.201.216.2');");
}
Member 7766180 19-Oct-11 16:48pm    
This fixed the undelared identifier...
SQLRETURN rc, TOTAL, QUOTA;
and I removed the int TOTAL and int QUOTA
Member 7766180 19-Oct-11 16:52pm    
Some kinda of a joke! Now the INSERT doesn't work!!!!!!!

if (rowCount >= 1)
{
printf("PASS\n");
SQLExecute("INSERT INTO tblDownloads (tblDownloads.[DownloadIP] , tblDownloads.[DownloadCount]) VALUES('173.201.216.2', 1);");

TOTAL = SQLFetch ("SELECT tblDownloads.[DownloadCount] WHERE tblDownloads.[DownloadIP] = '173.201.216.2';");
QUOTA = SQLFetch ("SELECT tblIP.[IPQuota], WHERE tblIPID.[IPAddress] = '173.201.216.2';");

if (TOTAL >= QUOTA)
{
SQLExecute ("UPDATE tblIP SET tblIP.[IPMax] WHERE tblIP.[IPAddress] = '173.201.216.2');");
}
I'm not sure if what I'm saying will solve you're problem as I've never worked with Access, but when doing this with SQL Server, I remember escaping the single quotes with another set of single quotes.
So the statement would end like - ... WHERE tblIPID.[IPAddress] = ''173.201.216.2'';"
 
Share this answer
 
Comments
Member 7766180 20-Oct-11 0:51am    
Thank you Superman, but what I have it seems to be working. I appreciate the input.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900