We help IT Professionals succeed at work.

table data manipulation

mfcjoshi
mfcjoshi asked
on
Hi,

I am a newcomer for database programming using mfc.

I am having database in sqlserver.database name is customers.table name is customer.

I want to execute the following query and depending on result of query I want to insert the records into table.

select status from customer where id=12 order by
date1 desc

 Here, if recordcount<1
 insert into table1(id,date1,status,location)

Please give me the step by step solution to solve the above problem.

Regards,
raj

values(x,getdate(),'IN','blr')
else
if  status='IN'
 y='out'
else y='IN'
end if

> insert into  table1(id,date1,status,location)
> values(x,getdate(),y,'blr')

> end if
Comment
Watch Question

BRONZE EXPERT
Author of the Year 2009

Commented:
1) Use ctl panel to create a DSN (if you don't already have one) to enable connection to the database.

2) In VC++ press Ctrl+W (class wizard) and click [Add Class> and New...

3) Set
Name: CRsCustomer
Base class CRecordset
[OK]

4) Leave ODBC radio set and drop down the list.  Your database connection will be listed.  Pick it.
Click OK

5) A list of tables in that database will appear.  Select Customer.

6) Back in the ClassWizard, click OK.  You now have a file named RsCustomer.cpp and RsCustomer.h in your file list.

7)Open StdAfx.h and add this line:

#include <afxdb.h>     // MFC extensions (including VB)

...if does not already appear in there.

continued...
BRONZE EXPERT
Author of the Year 2009

Commented:
8) in your program add code like this:

CMyDlg::OnButton1()
{

// select status from customer where id=12 order by date1 desc


CRsCustomer crs;
crs.m_strFilter= "id=12"; // your WHERE clause
crs.m_strSort= "date1 DESC";

BOOL fErr= crs.Open();
if (fErr) {
     // oh no!"
}

// at this point, crs already has the data from the
// first (only) record (no need to do crs.MoveNext().

CString s;
s.Format(" Status of rec id 12 is: %s", (LPCSTR)crs.m_status ); //.. assuming status in a varchar

MessageBox( s );

// Here, if recordcount<1
// insert into table1(id,date1,status,location)

if (crs.GetRecordCount() < 1 ) {
    crs.AddNew()
    crs.m_id= 13; // assuming it is an int
    crs.m_status="IN";
    crs.m_location="somewhere"
    crs.Update();
}

0--0-0-0-00--0-0-0-0-0
When you open a CRecordset, it automatically creates and opens a CDatabase and uses it internally.  There are many oyher options, but as you can see, the basics are quite simple.

9) You are done.  Your question is answered.  Select [Accept Comment as Answer] and click (o) Excellent.  and then click [Submit]

-- Dan

Author

Commented:
accept comment
BRONZE EXPERT
Author of the Year 2009
Commented:
hi mfcjosh,
>>accept comment
if my comment was useful to you, clease click the button labeled [Accept Comment as Answer] then grade the answer and submit it.

-- Dan

Explore More ContentExplore courses, solutions, and other research materials related to this topic.