合作联系
  wanwqing@vip.163.com
  0790-XXXXXXX
关于实验室
VC++对SQL Server数据库操作
发布者:实验室   时间:2019-01-28 12:19:40   浏览次数:156

在Stdafx.h中用#import引入ADO文件

#import "msado15.dll" no_namespace rename("EOF","adoEOF") rename("BOF","adoBOF")

 

类成员变量中定义:

_ConnectionPtr m_pConnection;

_RecordsetPtr m_pRecordset;

 

初始化部分:

CString strname;

//strname=_T("Provider=SQLOLEDB;Data Source=42.1.1.196;uid=sa;pwd=13879012783;Initial Catalog=stu;");//远程数据库

strname=_T("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=stu;Data Source=.");

//Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=%s;Data Source=.","ds");

try

{

AfxOleInit();

AfxEnableControlContainer();

m_pConnection.CreateInstance("ADODB.Connection");

_bstr_t strConnect=strname;

m_pConnection->Open(strConnect,"","",adModeUnknown);

}

catch(_com_error e)

{

AfxMessageBox(e.Description());

}

m_pRecordset.CreateInstance("ADODB.Recordset");

 

CString sql,s;

 

 

//sql.Format("SELECT * FROM tk where xkdm='%c'",a);//,m_fl);//分类

sql=_T("SELECT * FROM stud order by xm");

  if(m_pRecordset->State)   

         m_pRecordset->Close();

 

m_pRecordset->Open((_variant_t)sql,m_pConnection.GetInterfacePtr(),adOpenStatic,adLockOptimistic,adCmdText);

if(m_pRecordset->adoEOF)

AfxMessageBox(_T("你的数据库中还没有记录信息!"));

else

{

          CString m_tm=(_bstr_t)m_pRecordset->GetCollect("xm");

  m_pRecordset->MoveNext();

  CString m_tm1=(_bstr_t)m_pRecordset->GetCollect("xm");

  m_pRecordset->MoveNext();

  CString m_tm2=(_bstr_t)m_pRecordset->GetCollect("xm");

  m_pRecordset->MoveNext();

  CString m_tm3=(_bstr_t)m_pRecordset->GetCollect("xm");

   GetDlgItem(IDC_STATIC1)->SetWindowText(m_tm+m_tm1+m_tm2+m_tm3);

 

// m_dbgrid1.SetRefDataSource(theApp.m_pRecordset->DataSource);

}

CString bt[5]={_T("学号"),_T("姓名"),_T("性别"),_T("年龄"),_T("电话")};//表头字符串数组

int FldWidth[5]={90,80,50,70,90};//表头各列的宽度数组

CListCtrl *clv;

clv=(CListCtrl *)GetDlgItem(IDC_LIST1);

(void)clv->SetExtendedStyle(LVS_EX_FULLROWSELECT|LVS_EX_GRIDLINES);

    for(int j=0;j<5;j++)

      clv->InsertColumn(j,bt[j],LVCFMT_LEFT,FldWidth[j]);

int i=0,col_num=4;CString m3;

m_pRecordset->MoveFirst();

while(!m_pRecordset->adoEOF)

{

i=clv->GetItemCount();//list表格的行数

clv->InsertItem(i,_T(""));//在list表格上插入一个空行

_variant_t vtFld,vtIndex;

//通用的读取字段的方法,与数据表结构无关 这个方法很高明

for (int j=0;j<col_num;j++)

{

vtIndex.vt=VT_I2;

vtIndex.iVal=0;

vtFld=m_pRecordset->Fields->GetItem(long(j))->GetValue();

if (vtFld.vt!=VT_NULL)//如果字段内容不为空就读出来添加到list表格中

{

m3=(LPCSTR)_bstr_t(vtFld);

clv->SetItemText(i,j,m3);

}

}

m_pRecordset->MoveNext();

}

 

void Cvcdbr2Dlg::readDB(void)

{

  CString sql=_T("SELECT * FROM stud order by xm");

  if(m_pRecordset->State)   

         m_pRecordset->Close();

 

m_pRecordset->Open((_variant_t)sql,m_pConnection.GetInterfacePtr(),adOpenStatic,adLockOptimistic,adCmdText);

if(m_pRecordset->adoEOF)

AfxMessageBox(_T("你的数据库中还没有记录信息!"));

 

clv->DeleteAllItems();

int i=0,col_num=4;CString m3;

m_pRecordset->MoveFirst();

while(!m_pRecordset->adoEOF)

{

i=clv->GetItemCount();//list表格的行数

clv->InsertItem(i,_T(""));//在list表格上插入一个空行

_variant_t vtFld,vtIndex;

//通用的读取字段的方法,与数据表结构无关这个方法很高明

for (int j=0;j<col_num;j++)

{

vtIndex.vt=VT_I2;

vtIndex.iVal=0;

vtFld=m_pRecordset->Fields->GetItem(long(j))->GetValue();

if (vtFld.vt!=VT_NULL)//如果字段内容不为空就读出来添加到list表格中

{

m3=(LPCSTR)_bstr_t(vtFld);

clv->SetItemText(i,j,m3);

}

}

m_pRecordset->MoveNext();

}

}

void Cvcdby1Dlg::OnBnAddClickedButton2()

{

  CString sql,m1,m2,m3,x;

CEdit *ce;

ce = (CEdit *)GetDlgItem(IDC_EDIT1);

ce->GetWindowTextW(m1);

CEdit *ce2;

ce2 = (CEdit *)GetDlgItem(IDC_EDIT2);

ce2->GetWindowTextW(m2);

CEdit *ce3;

ce3 = (CEdit *)GetDlgItem(IDC_EDIT3);

ce3->GetWindowTextW(m3);

CEdit *ce4;

ce4 = (CEdit *)GetDlgItem(IDC_EDIT4);

ce4->GetWindowTextW(x);

sql.Format(_T("insert into stud(xh,xm,xb,nl) values('%s','%s','%s',%s)"),m1,m2,m3,x);

// sql=_T("insert into stud(xh,xm,xb,nl) values('220','zzz','na',20)");

_variant_t RecordsAffected;

m_pConnection->Execute((_bstr_t)sql,&RecordsAffected,adCmdText);

}

 

void Cvcdbr1Dlg::OnBnClickedButton4()

{

  CString sql,m1,m2,m3,x;

CEdit *ce;

ce = (CEdit *)GetDlgItem(IDC_EDIT1);

ce->GetWindowTextW(m1);

CEdit *ce2;

ce2 = (CEdit *)GetDlgItem(IDC_EDIT2);

ce2->GetWindowTextW(m2);

CEdit *ce3;

ce3 = (CEdit *)GetDlgItem(IDC_EDIT3);

ce3->GetWindowTextW(m3);

CEdit *ce4;

ce4 = (CEdit *)GetDlgItem(IDC_EDIT4);

ce4->GetWindowTextW(x);

sql.Format(_T("update stud set xm='%s',xb='%s',nl=%s where xh='%s'"),m2,m3,x,m1);

_variant_t RecordsAffected;

m_pConnection->Execute((_bstr_t)sql,&RecordsAffected,adCmdText);

 

}

===end==