Passing A Variable To SQL

[DSTR3A]

I have a variable that is an IP address. It is saved as text in my Access 2010 database. I am trying to run this query with ipSrc and the query always fails. My guess is that its seeing ipSrc as ipSrc and not as the actual IP address. I tried it with 'ipSrc' and just plain ipSrc and both reurn fail. Also tried ""ipSrc"", failed as well. This failed to. '&ipSrc'. Here is the statement.

SQLCHAR* query = (SQLCHAR*)"SELECT tblIP.[IPAddress], tblIP.[IPType], tblIP.[IPStatus], tblIP.[IPMax] FROM tblIP WHERE tblIP.[IPAddress]= ipSrc AND tblIP.[IPType]=3 AND tblIP.[IPStatus]=1 AND tblIP.[IPMax]=0;"; 

and here is the definition of ipSrc.

translate_ip(ip_header->source_ip, ipSrc);

Using printf it prints out as an actual IP address.

printf("\n   Source      IP: %s", ipSrc);

[Lamblion]

See if this works --

...tbIIP.[IPAddress]=\"ipSrc\" AND...

[DSTR3A]

Hey Lamblion! Thank you. I tried it and it still is not running as it should.
Here is a good link...
http://www.informit.com/library/content.aspx?b=Visual_C_PlusPlus&seqNum=182

[freddie1]

If the database field type is string*64 or whatever, then you are going to have to strcpy the value you want to search for into your SQL statement. For example...

SELECT tbl.[IPAddress], ........ WHERE tbl.[IPAddress='101.234,456.789' AND ....

[DSTR3A]

Thanks freddie1. It works fine when I do that, however I don't always know the IP address so I have to use ipSrc. That's where the IP address comes from.
works

WHERE tblIP.[IPAddress]= '101.234.456.789'

doesn't work

WHERE tblIP.[IPAddress]= 'ipSrc'

ipSrc is the variable

This also doesn't work...

WHERE tblIP.[IPAddress]= \'ipSrc\' 

[Lamblion]

Outstanding linek, DSTR3A. Thank you!!!

[freddie1]

You are aware that you need to use string concatenation functions to concatenate that actual ip address into your SQL string, don't you? I mean, you can't just stick the alphanumeric symbols into the string and expect the ODBC driver to know what ipSrc is (even if it is a valid variable and is indeed holding the string you want). If you put 'ipSrc' in the string it will be looking for a text string in the database with ipSrc in the string. Try it, you'll see.

[DSTR3A]

Thank you freddie1 I was just realising that. However; I don not know how to do this! Any help would be appreciated. Thank you.

[Lamblion]

DSTR3A, you declare a major string to hold all of the data and then pass that.

Here's an example from my own program --

/* Connection string for Direct ODBC */
	strcpy_s(szDSN,256,"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=");
	strcat_s(szDSN,256,(LPSTR)BOSSDIR);
	strcat_s(szDSN,256,"\\BOSS_Quotation.accdb;PWD=1L0v3Acce55;");

	/* Allocate an environment handle */
    rc = SQLAllocEnv(&hEQuotenv);

    /* Allocate a connection handle */
    rc = SQLAllocConnect(hEQuotenv, &hDQuotebc);

    /* Connect to the database */
    rc = SQLDriverConnect(hDQuotebc, NULL, (SQLCHAR*)szDSN,  _countof(szDSN), 
		szConnStrOut, 255, &iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);

where BOSSDIR above is the directory/folder of my main app. As you can see, I put everything into szDSN and then use that string.

szDSN is declared as char szDSN[256];

[DSTR3A]

fredie1, thank you. A little confused here. Are you saying that I have to do all this just to set one field as a string. There has to e an easier way? Thank you.

[Lamblion]

I was just showing you how to concantenate a string and pass it on. Your code will probably be different with regard to the SQL calls.

[DSTR3A]

Got it.

char buffer[1024] = {0,};
sprintf(buffer,"SELECT tblIP.[IPAddress], tblIP.[IPType], tblIP.[IPStatus], tblIP.[IPMax] FROM tblIP WHERE tblIP.[IPAddress]= '%s' AND tblIP.[IPType]=3 AND tblIP.[IPStatus]=1 AND tblIP.[IPMax]=0;",ipSrc);
SQLCHAR* query = (SQLCHAR*)buffer;

Thank you.

[Lamblion]

DSTR3A, if you and I were in the same room where we could talk to each other, I think we could create a very scary piece of code together!!! -:)

Then we'd have to have freddie1 bail us out!

[freddie1]

One useful thing to keep in mind is that, if you print out your query string (to debug output file or console window, as the case may be), i.e., 'buffer' in DSTR3A's code above, and you have difficulty getting it to execute correctly or at all, just paste it into the SQL View window in Access and click the yellow (it used to be yellow, anyway) exclamation point to execute it. If it won't execute that way, it won't in your code either. I do that all the time. In fact, that is usually how I construct all my queries, whether they be SELECT statements, UPDATE statements, INSERT statements, whatever.

[Lamblion]

It's a little blue right-pointing arrow in the VBA Editor in Access 2010. Since you program in VB, I will tell you right now that you are missing a LOT in VBA programming by not having Office 2010. They have enhanced virtually everything, so much so that I have ordered a book on VBA programming just so I can take more advantage of Acess 2010's expanded functionality.

In fact, this whole database thing in the three threads here have cost me a little over $100.00 in books! -:)

[DSTR3]

Its funny I knew access 2002 like the. Back of my hand and now im using 2010 and its like learning all over agaun! And putting it togther with C++! Having lots of fun I hope these threads helped you as much as it helped me!

[Lamblion]

Most definitely these threads have helped me. freddie1 got me kickstarted, otherwise I think it would have taken me a lot longer. I also bought these two books on Access 2010 --

http://www.amazon.com/Microsoft-Access-2010-Step/dp/0735626928/ref=sr_1_1?ie=UTF8&qid=1319545803&sr=8-1

http://www.amazon.com/Access-2010-Missing-Matthew-MacDonald/dp/1449382371/ref=sr_1_1?s=books&ie=UTF8&qid=1319545827&sr=1-1

I've been working through the first one and have found it very helpful.

Also, FWIW, over on the C++ forum I was told that the older books (late 90's, early 2000's) on database programming were still applicable, as not much has changed since then, so you might want to search on Amazon for "c++ database development" or "c++ database programming" and so on and see if anything strikes your fancy.

The book I bought from 1999 cost me $ 4.99. It was, of course, used, but like new, it says, so I'm looking forward to getting it.

Anyway, thanks to freddie1 and you I'm up and running, but still with a lot to learn.