DAO SQL. How?? Help.

[Guest_imported]

How can implement a SQL statement in DAO, i want to display information on record WHERE Name = m_Name (typed in edit box)

Thank U.

 

[Guest_imported]

Dear morse2,

First of All, you have to include the two classes required to implement DataBase functions: "afxdao.h" and "afxdisp.h".
Then you have to declare a variable of the type "CDaoDatabase", which you will use to open the database the following way:
CDaoDatabase db;
db.Open(path of the database);

The next step is declaring a string variable where you will store your query:

char StrSQL[200];
strcpy(StrSQL,"Select......"

if your query is an "Insert Into", a "Delete", or an "Update", just execute it like this:
db.Execute(StrSQL);

However, if it is a "Select", you have to declare a variable of the type CDaoRecordset. The result of your query will be stored in this variable.
then declare a variable of the type COleVariant:

CDaoRecordset rs;
COleVariant Ret;
.
.
.
rs.Open(dbOpenDynaset,_T(StrSQL));
rs.GetFieldValue(name of the field between quotation,ret1);

COleVariant is a struct that contains several fields, but only one of them will have the right value. It depends on the type of the field in the database. When you find out what it is, use a cast before it.
I hope I've been helpful.

 

[Mongooses]

If you already have constructed a DAO recordset then prior to opening the recordset set the member variable m_strFilter equal to the text in the edit box. Then open the recordset and the m_strFilter will act as the SQL WHERE statement.

HTH,

JC

 

[Guest_imported]

what am i doing wrong there pls help.

CDoaDatabase* pDb = Get Database();
TableSet recSet;
try
{
CString strSelect(_T("Select * From tblItem Where ItemName = P790&quot);
recSet.Open(dbOpenDynaset,strSelect);
// fill dialog box
m_Name = recSet.m_IName;
m_code = recSet.m_ICode;
}

Upon running i am getting error " too few parameters.Expected 1)

& how can i get user input ( m_usIn) passed to the query, would not except
(_T("Select * From tblItem Where ItemName =",m_usIn));

pls help...

 

[issahar]

and why not to use Dao 3.6 in place of waking up the old AGONYZING MFC MONSTER???
))

just create instance of DBEngine object, call its OpenDatabase method , get interface on DataBase and call the OpenRecordSet method with the required query???

Deploy 3.6 is the easyest thing in the world - only one registered dll : C:\Program Files\Common Files\Dao\Dao360.dll (as usual you have it as part of service pack 3 of VStudio)

I am using this component with a great effect in my Visual Basic modules, but there is no problem to do that from C++

Please let me know if this helped.

My best regards, Issahar

 

[Zyrenthian]

Sorry if this is a repeat answer. Busy today so just typed it up quick

CString SQL;
SQL.Format("SELECT * FROM [DATABASE NAME] WHERE Name = '" + m_Name + "'";

Matt

 

[Guest_imported]

what is this junk.....
very confusing......

 

[Guest_imported]

Sorry Zyrenthian it lokks like we were both typing messages at the same time, when i said "what is this junk,very confusing " this were ment for Issahar reply..
and my message got posted after your..
Sorry again..

 

[Guest_imported]

Answer to "what am i doing wrong there pls help" :

Morse, if the error "too few parameters.Expected 1" occurs, there's only one reason. Your SQL query isn't right.
This is a run-time error, isn't it? It means that there are no promlems with your source code.
Insert a break point where the SQL query is about to be executed and, before running it, check if the query is correct, if there isn't any missing or extra comma and make sure that all the fields in the query do exist in the database.

 

[issahar]

For morse2...
what is so confusing for you in my message?
I just wanted to say that I don't advice using MFC objects like CDaoDatabase or CDaoRecordSet...that's all...
))

...
because MFC is going to agonize in next versions of Visual Studio...

Issahar