asked on

table data manipulation

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

DanRollins

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...

DanRollins

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

mfcjoshi

ASKER

accept comment

ASKER CERTIFIED SOLUTION

DanRollins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial