SQL Statements Not Executing

Question

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 19-Oct-11 8:41am

Member 7766180

Updated 19-Oct-11 13:48pm

v5

Add a Solution

2 solutions

Solution 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'';"

Posted 19-Oct-11 16:46pm

«_Superman_»

Comments

Member 7766180 20-Oct-11 0:51am

Thank you Superman, but what I have it seems to be working. I appreciate the input.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

fjdiewornncalwe · Accepted Answer · 2011-10-19T09:46:00

Solution 1

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.

Posted 19-Oct-11 9:46am

fjdiewornncalwe

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');");
}

Member 7766180 19-Oct-11 21:20pm

INSERT working.
he next two problems are the Sum and Select statements.

SQLCHAR* query3 = (SQLCHAR*)"SELECT DownloadIP, Sum(DownloadCount) AS SumOfDownloadCount FROM tblDownloads GROUP BY DownloadIP;";

SQLCHAR* query4 = (SQLCHAR*)"SELECT tblIP.IPAddress, tblIP.IPQuota FROM tblIP WHERE (((tblIP.IPAddress)='173.201.216.2'));";

Member 7766180 19-Oct-11 21:58pm

Next One Working...

TOTAL = SQLAllocEnv(&hEnv3);
TOTAL = SQLAllocConnect(hEnv3, &hDbc3);
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);
while (SQL_SUCCEEDED(TOTAL))
{
TOTAL = SQLFetch(hStmt3);
rowCount3++;
};
TOTAL = SQLFreeStmt(hStmt3, SQL_DROP);
printf("Total of Downloads: %d\n",rowCount3);
SQLDisconnect(hDbc3);

SQLFreeHandle(SQL_HANDLE_DBC, hDbc3);

SQLFreeHandle(SQL_HANDLE_ENV, hEnv3);

Two more left!!!!

Member 7766180 20-Oct-11 0:51am

One More Left!!!