How to easily access a database(Access, SQL ,etc) through ADO in VC++

There are several ways you could use DAO(fastest but only singlethreaded), could use the raw ODBC DLL, or you could do what I'm doing, which seems easiest, in a method that you don't even have to declare the recordset format , using ADO.

[tt]
#import "C:\Program Files\Common Files\System\ADO\msado15.dll"
rename_namespace("ADOCG") rename("EOF","EndOfFile")
using namespace ADOCG;
[/tt]

that'll include the ADO libraries

[tt]_ConnectionPtr m_pConnection;[/tt]
this will declare a connection pointer to the database(whenever creating and assigning something to a recordset pointer, you'll either need to give it a connection pointer, or tell it where to connect)
[tt]m_pConnection.CreateInstance( __uuidof(Connection) );[/tt]
this will initialize it for usage

[tt]
::CoInitialize(NULL); [/tt]
[whenever working with recordsets I recommend initializing the COM environment
before entering the function and ::CoUninitialize();
before leaving a function]
[tt]
_RecordsetPtr m_pRecordSet;
m_pRecordSet.CreateInstance( __uuidof(Recordset) ); [/tt]
(as you can see connection and recordset declaring, and initializing is
pretty similar)

Once you got a connection open(you could make this global and have several
recordsets use the same connection)
(But in my situation, such as a multithreaded server , I assign a connection
pointer to each thread which is turns has a recordset)
[tt]
m_pConnection->Open(L"DSN=ldap", L"", L"", -1);
[/tt]
this is the method I use, I know you can use the name of the database(aka DNS-less connection) but the L"DSN=ldap" points to the DSN already in the ODBC32 control(located in control panel) I've defined a system-wide link to the database and named it ldap (this makes it much easier than defining the path in vC++ all the time, just
linking to the DSN name helps a lot)

now there are several ways you can do this to open and read info from a recordset I use Lots of SQL commands when opening a recordset (SQL Query commands really help in determining a criteria for what's selected in the recordset)

for example:
[tt]
int CUT_FTPThread::OnCheckPassword(struct sUsers &CkPass)
{
::CoInitialize(NULL); _RecordsetPtr m_pRecordSet;
m_pRecordSet.CreateInstance( __uuidof(Recordset) );
int rt = FALSE; char sqlexec[255];
if((lstrlen(CkPass.Password) <1) || (lstrlen(CkPass.Email) < 1)) return FALSE;

wsprintf(sqlexec,"select * from Users where Email = '%s' and Password = '%s'", CkPass.Email, CkPass.Password);
m_pRecordSet->Open( sqlexec, m_pConnection.GetInterfacePtr(), adOpenDynamic, adLockOptimistic, adCmdText );

if(!m_pRecordSet->EndOfFile)
{
strncpy(m_user,CkPass.Email,MAX_PATH-1);
strcpy(CkPass.UID,FieldToChar(m_pRecordSet,L"UserID"));
m_pRecordSet->Close(); ::CoUninitialize(); return TRUE;
}
m_pRecordSet->Close();
::CoUninitialize();
return FALSE;
}
[/tt]
a simple "select * from TableNameHere" will store all the records and fields from that table into the recordset
you could do "Select FieldName from Table" to get just a field of all the records same with "select field1,field2 from table" you can select multiple tables, but I wont get into that right now the FieldToChar() command is something I made, to retrieve the variant values, makes it easier for me
[tt]
char* CUT_FTPThread::FieldToChar(_RecordsetPtr recset, _variant_t Fieldname)
{
_variant_t tmpvariant; char tmpChar[255];
tmpvariant = recset->GetCollect(Fieldname);
strcpy(tmpChar,(_bstr_t)tmpvariant);
return (tmpChar);
}
[/tt]
You'll get a warning with this about returning pointer of local variable, but it works as far as I've worked with it
just don't assign the reference, make it return the value, not it's address then you'll be fine

Inserting & Deleting:

[tt]
int CUT_FTPThread::InsertLDAP(LPSTR userid, LPSTR pcid, LPSTR ipAddress, int
online)
{
::CoInitialize(NULL); _RecordsetPtr m_pRecordSet;
m_pRecordSet.CreateInstance( __uuidof(Recordset) );
char sqlexec[255];

wsprintf(sqlexec,"delete * from LDAP where PCID = %s", pcid);
m_pRecordSet->Open(sqlexec , m_pConnection.GetInterfacePtr(), adOpenDynamic,
adLockOptimistic, adCmdText );

if(online)
{wsprintf(sqlexec,"Insert into LDAP (PCID, UID, Online, IP) values
(%s,%s,1,'%s')",pcid, userid,ipAddress);}
else
{wsprintf(sqlexec,"Insert into LDAP (PCID, UID, Online, IP) values
(%s,%s,0,'%s')",pcid, userid,ipAddress);}

m_pRecordSet->Open(sqlexec, m_pConnection.GetInterfacePtr(), adOpenDynamic,
adLockOptimistic, adCmdText );
::CoUninitialize(); return TRUE;
}
[/tt]

Getting a Maximum value:
[tt]m_pRecordSet->Open( "select max(UserID) as MaxUID from Users",m_pConnection.GetInterfacePtr(), adOpenDynamic, adLockOptimistic,adCmdText );
[/tt]
almost all these other commands are in the help files, or located at MSDN for SQL Servers, and should be located in the Access97(at least) help file
got more questions let me know