Database Question 2

SmileeTiger · Mar 13, 2000

I have created a password table within my database (created in MS access) and I want to have VC++ open this table and check to see if there is an entry for a particular User ID and Password that is entered in a text box. If there is a entry in the table the program retrive the access level from the database and but it in a variable for later use. Does anyone have any idea how to do this? 
 
FYI 
Text Boxes 
UserID: IDC_CardID 
Password: IDC_PIN 
 
The name of the database is: Student Registration System I have already registered the database with ODBC and I have retrieved data from the database elsewhere in my program. 
 
The data i want is in the passwords table in that database. So the cadrID should be chacked against the username field and the Password should be checked against the password field. If the match the accesslevel field coresponding to that record should be returned otherwise the program should display something like: AfxMessageBox("Bad Log In Attempt: Please try again&quot

; 
Thanks in advance for your help

Karl Blessing · Mar 14, 2000

There're are several ways you could ue DAO(fastest but only singlethreaded), could use thr raw ODBC dll, or you could do what i'm doing, 
which seems easiest, in a method that you dont even have to declare the recordset format , using ADO. 
 
#import "C:\Program Files\Common Files\System\ADO\msado15.dll" rename_namespace("ADOCG&quot

rename("EOF","EndOfFile&quot

using namespace ADOCG; 
 
that'll include the ADO libraries 
 
_ConnectionPtr m_pConnection; 
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 connectino pointer, or tell it where to connect) 
m_pConnection.CreateInstance( __uuidof(Connection) ); 
this will initialize it for usage 
 
 
::CoInitialize(NULL); 
[whenever working with recordsets i recomend initializing the COM enviroment before entering the function and ::CoUninitialize(); 
before leaving a function] 
 
_RecordsetPtr m_pRecordSet; 
m_pRecordSet.CreateInstance( __uuidof(Recordset) ); 
 
(as you can see connectino and recordset declaring, and initializing is pretty similar) 
 
Once you got a connectino 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) 
 
m_pConnection->Open(L"DSN=ldap", L"", L"", -1); 
 
this it eh method i use, i know you can use the name of the database 
but the L"DSN=ldap" means in the ODBC32 control(located in control pannel) 
i've defined a systemwide 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 alot) 
 
now theres 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 whats seleted in the recordset) 
 
for example: 
 
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&quot

); 
m_pRecordSet->Close(); ::CoUninitialize(); return TRUE; 
} 
m_pRecordSet->Close(); 
::CoUninitialize(); 
return FALSE; 
} 
 
a simple "select * from TableNameHere" will store all the records and feilds from that table into the recordset 
you could do "Select FieldName from Table" to get just a feild of all the records 
same with "select field1,field2 from table" 
youc an select multiple tables, but i wont get into that right now 
 
the FieldToChar() command is something i made, to retreive the varient values, makes it easier for me 
 
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); 
} 
 
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 dont assign the reference, make it return the value, not it's addresse then you'll be fine 
 
Inserting & Deleting: 
 
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; 
} 
 
 
Getting a Maximum value: 
m_pRecordSet->Open( "select max(UserID) as MaxUID from Users", m_pConnection.GetInterfacePtr(), adOpenDynamic, adLockOptimistic, adCmdText ); 
 
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 Karl <a href=mailto:kb244@bellsouth.net>kb244@bellsouth.net</a> <a href=

http://kb244.8m.com>

</a> Experienced in , or have messed with : VC++, Borland C++ Builder, VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(hehe, yea it was 4.5 too, least i didnt start with COBOL)

SmileeTiger · Mar 14, 2000

Thanks for your help but I still have a few problems. Below is what I have done so far.. 
 
_ConnectionPtr m_pConnection; //Declares a connection pointer to the database 
//Creates an instance of the connection 
m_pConnection.CreateInstance( __uuidof(Connection) ); 
m_pConnection->Open(L"DSN=Student Registration System", L"", L"", -1); 
//this should go in a function when it works 
int m_AccessLevel; 
::CoInitialize(NULL); _RecordsetPtr m_pRecordSet; 
m_pRecordSet.CreateInstance( __uuidof(Recordset) ); 
int rt = FALSE; char sqlexec[255]; 
wsprintf(sqlexec,"select AccessLevel from Password where UserName = '%s' and Password = '%s'", m_CardID, m_PIN); 
m_pRecordSet->Open( sqlexec, m_pConnection.GetInterfacePtr(), adOpenDynamic, adLockOptimistic, adCmdText ); 
if(!m_pRecordSet->EndOfFile) 
{ 
//strncpy(m_CardID,CkPass.Email,MAX_PATH-1); 
strcpy(m_AccessLevel,FieldToChar(m_pRecordSet,L"AccessLevel&quot

); 
m_pRecordSet->Close(); ::CoUninitialize(); 
 
} 
m_pRecordSet->Close(); 
::CoUninitialize(); 
 
The particular problem that I am having is that I am not sure what exactly to put inside if(!m_pRecordSet->EndOfFile){} 
I assume that where UserName = '%s' and Password = '%s'", m_CardID, m_PIN); selected the record that has the CardID and PIN that were entered into the text box. 
so:if(!m_pRecordSet->EndOfFile){} should put the value of AccessLevel from the table into the variable m_AccessLevel (it's an int). 
 
Also one other thing that I came across is that for the 
prototype char* FieldToChar(_RecordsetPtr recset, _variant_t Fieldname); is says that ....registrationsystem\registrationview.h(91) : error C2061: syntax error : identifier '_RecordsetPtr' 
 
Thanks again for all your help. 
 
Cory

Karl Blessing · Mar 14, 2000

you might need to include 
#include <icrsint.h> 
the if(!m_pRecorset->EOF...) that basicaly test to make sure the recordset isnt 
empty 
you could even use that same thing in a While loop, if you wanted to go thru the 
entire records, using ->next and so on, and it'll exit when it hits the last record 
Let me know if this works. Karl <a href=mailto:kb244@bellsouth.net>kb244@bellsouth.net</a> <a href=

http://kb244.8m.com>

</a> Experienced in , or have messed with : VC++, Borland C++ Builder, VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(hehe, yea it was 4.5 too, least i didnt start with COBOL)

SmileeTiger · Mar 14, 2000

I am still having some trouble. I didn't use your conversion function since I don't really need it (I think). 
 
I am now trying to display the Access Level on the screen of the user based upon his login name and password. 
 
A sample login and password would be UserID: 1 Password: adminpw 
 
In my database all values are text. 
 
When I try to login to the system however, the program only displays the number -85899346 in the text box. 
Any ideas? 
 
Below is the code I am using: 
_ConnectionPtr m_pConnection; //Declares a connection pointer to the database 
//Creates an instance of the connection 
m_pConnection.CreateInstance( __uuidof(Connection) ); 
m_pConnection->Open(L"DSN=Student Registration System", L"", L"", -1); 
//this should go in a function when it works 
char* m_AccessLevel; 
::CoInitialize(NULL); _RecordsetPtr m_pRecordSet; 
m_pRecordSet.CreateInstance( __uuidof(Recordset) ); 
int rt = FALSE; char sqlexec[255]; 
wsprintf(sqlexec,"select AccessLevel from Password where UserName = '%s' and Password = '%s'", m_CardID, m_PIN); 
m_pRecordSet->Open( sqlexec, m_pConnection.GetInterfacePtr(), adOpenDynamic, adLockOptimistic, adCmdText ); 
if(!m_pRecordSet->EndOfFile) 
{ 
//strncpy(m_CardID,CkPass.Email,MAX_PATH-1); 
strcpy(m_AccessLevel,(char*)L"AccessLevel&quot

; 
m_pRecordSet->Close(); ::CoUninitialize(); 
 
} 
m_pRecordSet->Close(); 
::CoUninitialize(); 
 
m_Test=(int)m_AccessLevel; 
 
Note: m_test is the variable that I have linked to the text box and it is of the type int. 
 
Thanks again for your help 
Cory M Hicks 
 
BTW I am posting this info both on the message bord and in a email just respond to one of them. I am putting it on the list in case anyone else has some ideas.

Karl Blessing · Mar 14, 2000

Do you have the Access Database pointed to correctly in the ODBC driver setup 
under control pannel , and dont have spaces, make the name simple, all lower case, 
single worded if possible 
 
also you should ::CoInitialize before the connectino as well, probally didnt make that 
clear 
 
and you need my function 
all yer doing is assigning the addresse of the literal "AccessLevel" right into the string 
pointer 
my function, retreives the actual text value from the database, then reutnrs a pointer 
to the actual string 
L is not a function, it allows the charater array to be passed to the functino as a 
variant. 
also for an integer, typecasting a charater array to an int, is inadvisible 
use atoi(character) in place of (int)char 
it'll return a interger 
Later tonight when i get home, I might be able to resquest a small sample of the database, and i'll try writing the code with your database. Karl <a href=mailto:kb244@bellsouth.net>kb244@bellsouth.net</a> <a href=

http://kb244.8m.com>

</a> Experienced in , or have messed with : VC++, Borland C++ Builder, VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(hehe, yea it was 4.5 too, least i didnt start with COBOL)

Karl Blessing · Mar 14, 2000

oh btw, disregard that part about the DSN , if you were able to attach the recordset without it crashing, it might have linked ok then Karl <a href=mailto:kb244@bellsouth.net>kb244@bellsouth.net</a> <a href=

http://kb244.8m.com>

</a> Experienced in , or have messed with : VC++, Borland C++ Builder, VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(hehe, yea it was 4.5 too, least i didnt start with COBOL)

Karl Blessing · Mar 14, 2000

Oh almost forgot, make sure its under System DSN, not User DSN Karl <a href=mailto:kb244@bellsouth.net>kb244@bellsouth.net</a> <a href=

http://kb244.8m.com>

</a> Experienced in , or have messed with : VC++, Borland C++ Builder, VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(hehe, yea it was 4.5 too, least i didnt start with COBOL)

Karl Blessing · Mar 14, 2000

On mine i got mine setup as "Data Source Name " is ldap 
the description is blank, the database is an Access97 db 
, under database, i have it pointing to c:\....\ldap.mdb 
and System Database, i chose none Karl <a href=mailto:kb244@bellsouth.net>kb244@bellsouth.net</a> <a href=

http://kb244.8m.com>

</a> Experienced in , or have messed with : VC++, Borland C++ Builder, VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(hehe, yea it was 4.5 too, least i didnt start with COBOL)

Karl Blessing · Mar 14, 2000

Ok I've rewritten your code 
 
// This is a fresh "MFC capable" console app made in VC++, the only important part 
is the 
// the declarations added, and the part after the Else, which is where the actual 
program starts 
// this may have some errors in it, i just fixed it up the database i used from your code 
is as followed 
// it is called SRS.mdb, but i linked it with the DSN name you chosen 
// also it has a single table called Password, which has 3 items in it UserName, 
Password, AccessLevel, all text 
// I've gotten a Access Violation after the entire program is done, probally because 
of this console mode, i dont know 
// you possibly might nto get it, but i have verified, it retrived the AccessLevel 
Sucessfully 
 
#include "stdafx.h" 
#include "ADOrvw.h" 
///important declarations 
#import "C:\Program Files\Common Files\System\ADO\msado15.dll" 
rename_namespace("ADOCG&quot

rename("EOF","EndOfFile&quot

using namespace ADOCG; 
#include <icrsint.h> 
//end of added declarations 
#ifdef _DEBUG 
#define new DEBUG_NEW 
#undef THIS_FILE 
static char THIS_FILE[] = __FILE__; 
#endif 
 
///////////////////////////////////////////////////////////////////////////// 
// The one and only application object 
 
CWinApp theApp; 
 
using namespace std; 
 
int _tmain(int argc, TCHAR* argv[], TCHAR* envp[]) 
{ 
int nRetCode = 0; 
 
// initialize MFC and print and error on failure 
if (!AfxWinInit

:GetModuleHandle(NULL), NULL, ::GetCommandLine(), 0)) 
{ 
// TODO: change error code to suit your needs 
cerr << _T("Fatal Error: MFC initialization failed&quot

<< endl; 
nRetCode = 1; 
} 
else 
{ 
char m_CardID[25], m_PIN[25]; 
strcpy(m_CardID,"K&quot

; strcpy(m_PIN, "LKM&quot

; 
::CoInitialize(NULL); 
_ConnectionPtr m_pConnection; m_pConnection.CreateInstance( 
__uuidof(Connection) ); 
m_pConnection->Open(L"DSN=Student Registration System", L"", L"", -1); 
 
char m_AccessLevel[128]; //has to be able to store the value, not point to it 
_RecordsetPtr m_pRecordSet; m_pRecordSet.CreateInstance( 
__uuidof(Recordset) ); 
int rt = FALSE; char sqlexec[255]; 
wsprintf(sqlexec,"select AccessLevel from Password where UserName = '%s' and 
Password = '%s'", m_CardID, m_PIN); 
m_pRecordSet->Open( sqlexec, m_pConnection.GetInterfacePtr(), 
adOpenDynamic, adLockOptimistic, adCmdText ); 
if(!m_pRecordSet->EndOfFile) 
{ 
//strncpy(m_CardID,CkPass.Email,MAX_PATH-1); 
//the following is basically the same thing as my FieldToChar function 
_variant_t tmpvariant; 
tmpvariant = m_pRecordSet->GetCollect(L"AccessLevel&quot

; 
strcpy(m_AccessLevel,(_bstr_t)tmpvariant); 
// instead of doing three lines, my function, lets you do the copy right in the strcpy, 
that way if yer getting lots 
// and lots of values, this would greatly simplfy your typing look below as i 
commented the function if you want to use it 
//m_pRecordSet->Close(); ::CoUninitialize(); already gets uninitilized and closed 
anyways at the end of the program 
} 
m_pRecordSet->Close(); 
m_pConnection->Close(); //must close connection as well at the end 
::CoUninitialize(); 
//m_Test=atoi(m_AccessLevel); //more efficient, and safter than typcasting in my 
opinion 
/* 
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); 
} 
*/ 
 
} 
 
return nRetCode; 
} 
Karl <a href=mailto:kb244@bellsouth.net>kb244@bellsouth.net</a> <a href=

http://kb244.8m.com>

</a> Experienced in , or have messed with : VC++, Borland C++ Builder, VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(hehe, yea it was 4.5 too, least i didnt start with COBOL)

SmileeTiger · Mar 14, 2000

Ok this orks fine as long as I have the entry Username:k Password:lkm in my passwords table... but I still need to retrieve the data (usernema and password) from the text fields. How would I do that? 
 
Oh and I assume the line m_Test=atoi(m_AccessLevel); //more efficient, and safter than whould not be commented out right? 
 
Cory

SmileeTiger · Mar 14, 2000

To phrase that better... I need to take the data from the username and password text fields and use those for the searching.

Karl Blessing · Mar 14, 2000

Modify the code for your use by replacing the variables or assinging to them in other ways 
 
for the other values, you'll need to change your Select statement to like 
select * from permisions 
(this will load every single record into the recordset) 
or if you just do 
select * from Password where User =.... 
this will put all feilds into the recordset with matching criteria 
 
_variant_t tmpvariant; 
tmpvariant = m_pRecordSet->GetCollect(L"NameOfField&quot

; 
strcpy(YourCharaterArray,(_bstr_t)tmpvariant); 
Karl <a href=mailto:kb244@bellsouth.net>kb244@bellsouth.net</a> <a href=

http://kb244.8m.com>

</a> Experienced in , or have messed with : VC++, Borland C++ Builder, VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(hehe, yea it was 4.5 too, least i didnt start with COBOL)

Karl Blessing · Mar 14, 2000

CStrings would be fine, just make this minor change 
wsprintf(sqlexec,"select AccessLevel from Password where UserName = '%s' and 
Password = '%s'",(LPSTR&)m_CardID,(LPSTR&)m_PIN); 
need to type cast the CStrings to (LPSTR&) 
Karl <a href=mailto:kb244@bellsouth.net>kb244@bellsouth.net</a> <a href=

http://kb244.8m.com>

</a> Experienced in , or have messed with : VC++, Borland C++ Builder, VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(hehe, yea it was 4.5 too, least i didnt start with COBOL)

SmileeTiger · Mar 14, 2000

After a webchat Karl showed me what was wrong with my code. Here is the final solution to my problem 
 
void CRegistrationView::OnButton3() 
{ 
::CoInitialize(NULL); 
_ConnectionPtr m_pConnection; m_pConnection.CreateInstance(__uuidof(Connection) ); 
m_pConnection->Open(L"DSN=Student Registration System", L"", L"",-1); 
 
char m_AccessLevel[128]; //has to be able to store the value, not point to it 
_RecordsetPtr m_pRecordSet; 
m_pRecordSet.CreateInstance(__uuidof(Recordset) ); 
int rt = FALSE; 
char sqlexec[255]; 
 
m_CardIdBox.GetWindowText(m_CardID); 
m_PINBox.GetWindowText(m_PIN); 
//converts from cstring to char* 
wsprintf(sqlexec,"select AccessLevel from Password where UserName ='%s' and Password = '%s'",(LPSTR&)m_CardID,(LPSTR&)m_PIN); 
m_pRecordSet->Open( sqlexec, m_pConnection.GetInterfacePtr(),adOpenDynamic, adLockOptimistic, adCmdText ); 
if(!m_pRecordSet->EndOfFile) 
{ 
_variant_t tmpvariant; 
tmpvariant = m_pRecordSet->GetCollect(L"AccessLevel&quot

; 
strcpy(m_AccessLevel,(_bstr_t)tmpvariant); 
 
} 
m_pRecordSet->Close(); 
m_pConnection->Close(); //must close connection as well at the end 
::CoUninitialize(); 
m_Test=atoi(m_AccessLevel); //more efficient, and safter than typcasting in my opinion 
 
UpdateData(FALSE); 
 
 
 
} 
 
 
Thanks again karl!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Database Question 2

SmileeTiger

Programmer

Karl Blessing

Programmer

SmileeTiger

Programmer

Karl Blessing

Programmer

SmileeTiger

Programmer

Karl Blessing

Programmer

Karl Blessing

Programmer

Karl Blessing

Programmer

Karl Blessing

Programmer

Karl Blessing

Programmer

SmileeTiger

Programmer

SmileeTiger

Programmer

Karl Blessing

Programmer

Karl Blessing

Programmer

SmileeTiger

Programmer

Similar threads

Part and Inventory Search

Sponsor