合作联系
  wanwqing@vip.163.com
  0790-XXXXXXX
关于实验室
VC++对Access数据库增删改查方法
发布者:实验室   时间:2019-01-28 12:13:48   浏览次数:243

BOOL CDb2Dlg::OnInitDialog()
{
 CDialog::OnInitDialog();

 // Add "About..." menu item to system menu.

 // IDM_ABOUTBOX must be in the system command range.
 ASSERT((IDM_ABOUTBOX & 0xFFF0) == IDM_ABOUTBOX);
 ASSERT(IDM_ABOUTBOX < 0xF000);

 CMenu* pSysMenu = GetSystemMenu(FALSE);
 if (pSysMenu != NULL)
 {
  CString strAboutMenu;
  strAboutMenu.LoadString(IDS_ABOUTBOX);
  if (!strAboutMenu.IsEmpty())
  {
   pSysMenu->AppendMenu(MF_SEPARATOR);
   pSysMenu->AppendMenu(MF_STRING, IDM_ABOUTBOX, strAboutMenu);
  }
 }

 // Set the icon for this dialog.  The framework does this automatically
 //  when the application's main window is not a dialog
 SetIcon(m_hIcon, TRUE);   // Set big icon
 SetIcon(m_hIcon, FALSE);  // Set small icon

 // TODO: Add extra initialization here
//============================================================================== 
//
//                 从这里开始是自己写的代码,以上是系统生成的代码
//
//==============================================================================

 CString strname;
//连接本地Access数据库
strname.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\db1.mdb;","","",adModeUnknown);
//连接数据库的连接串,其中db1.mdb是数据库的名字
 try
 {
 AfxOleInit();//OLE初始化
 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");//创建数据表连接器实例,只要创建一次实例

    InitListView(); //初始化列表控件  
                if (LinkTable())//如果打开数据表发现有数据
    {
     ReadData();//就读取数据到EDIT控件
     ReadData2ListView();//读取数据到ListView控件
    }
 return TRUE;  // return TRUE  unless you set the focus to a control
}
void CDb2Dlg::InitListView()//初始化列表控件
{
CString bt[5]={"学号","姓名","性别","班级","电话"};//表头字符串数组
int FldWidth[5]={70,60,50,70,90};//表头各列的宽度数组

    (void)m_list.SetExtendedStyle( LVS_EX_FULLROWSELECT|LVS_EX_GRIDLINES );
    for (int j=0;j<5;j++)
       m_list.InsertColumn(j,bt[j] , LVCFMT_LEFT, FldWidth[j]);

}

void CDb2Dlg::OnSysCommand(UINT nID, LPARAM lParam)
{
 if ((nID & 0xFFF0) == IDM_ABOUTBOX)
 {
  CAboutDlg dlgAbout;
  dlgAbout.DoModal();
 }
 else
 {
  CDialog::OnSysCommand(nID, lParam);
 }
}

// If you add a minimize button to your dialog, you will need the code below
//  to draw the icon.  For MFC applications using the document/view model,
//  this is automatically done for you by the framework.

void CDb2Dlg::OnPaint() 
{
 if (IsIconic())
 {
  CPaintDC dc(this); // device context for painting

  SendMessage(WM_ICONERASEBKGND, (WPARAM) dc.GetSafeHdc(), 0);

  // Center icon in client rectangle
  int cxIcon = GetSystemMetrics(SM_CXICON);
  int cyIcon = GetSystemMetrics(SM_CYICON);
  CRect rect;
  GetClientRect(&rect);
  int x = (rect.Width() - cxIcon + 1) / 2;
  int y = (rect.Height() - cyIcon + 1) / 2;

  // Draw the icon
  dc.DrawIcon(x, y, m_hIcon);
 }
 else
 {
  CDialog::OnPaint();
 }
}

// The system calls this to obtain the cursor to display while the user drags
//  the minimized window.
HCURSOR CDb2Dlg::OnQueryDragIcon()
{
 return (HCURSOR) m_hIcon;
}
void CDb2Dlg::ReadData2ListView()//读数据到ListView
{

    int col_num;//字段的个数
       col_num=5;
    
       m_list.DeleteAllItems();
 
       UpdateData(true);
    _RecordsetPtr m_pRecordset1;
    m_pRecordset1.CreateInstance("ADODB.Recordset");
    CString sql;
 
       sql="SELECT * FROM stu"; 
       m_pRecordset1->Open(_variant_t(sql),_variant_t((IDispatch*)m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
 
    CString m3;
    _variant_t   var;   
    int i;
while(!m_pRecordset1->adoEOF)
{
        i=m_list.GetItemCount();//list表格的行数
        m_list.InsertItem(i,"");//在list表格上插入一个空行
   
   _variant_t vtFld,vtIndex;
 
   //通用的读取字段的方法,与数据表结构无关  这个方法很高明
   for (int j=0;j<col_num;j++)
   {
   vtIndex.vt=VT_I2;
   vtIndex.iVal=0;
   vtFld=m_pRecordset1->Fields->GetItem(long(j))->GetValue();
   if (vtFld.vt!=VT_NULL)//如果字段内容不为空 就读出来添加到list表格中
   {  
    m3=(LPCSTR)_bstr_t(vtFld);
    m_list.SetItemText(i,j,m3);
   } 
   }
 m_pRecordset1->MoveNext();
}
UpdateData(false);
m_pRecordset1->Close();///关闭记录集

 

}
int CDb2Dlg::LinkTable()//连接数据表
{
 CString sql="SELECT * FROM stu";//查询语句
 if(m_pRecordset->State)   //判断数据表连接器状态
       m_pRecordset->Close(); //如果是开着的就关掉

              m_pRecordset->Open((_variant_t)sql,m_pConnection.GetInterfacePtr(),adOpenStatic,adLockOptimistic,adCmdText);//打开数据表
              if(m_pRecordset->adoEOF)//判断数据表是否为空数据表
              {
                     AfxMessageBox("你的数据表中还没有记录信息!");
      return 0;
              }
     else return 1;

}
void CDb2Dlg::ReadData()//读取 单条记录
{
 // m_pRecordset.CreateInstance("ADODB.Recordset");//创建数据表连接器实例
 CString  m;
 m=(char *)(_bstr_t)m_pRecordset->GetCollect("xh"); //读取学号字段的内容  
 GetDlgItem(IDC_EDIT1)->SetWindowText(m);   //将读到的内容放进Edit1编辑框中

 m=(char *)(_bstr_t)m_pRecordset->GetCollect("xm");//读取姓名字段的内容
 GetDlgItem(IDC_EDIT2)->SetWindowText(m);  
  
//布尔字段 性别 读取
   _variant_t  var;   
   CString   str;   
   var   =   m_pRecordset->Fields->GetItem(long(2))->GetValue();
   //str   =   (var.boolVal   ==   0)   ?   "女"   :   "男";   
   //GetDlgItem(IDC_EDIT3)->SetWindowText(str);  显示性别

   if (var.boolVal   ==   0)// 是0 让单选钮  "女" 被选择
    CheckRadioButton(IDC_RADIO1,IDC_RADIO2,IDC_RADIO2);
   else       // 非0 让单选钮  "男" 被选择
    CheckRadioButton(IDC_RADIO1,IDC_RADIO2,IDC_RADIO1);

//日期字段读取
   var   =m_pRecordset->Fields->GetItem(long(3))->GetValue();; 
   str   =COleDateTime(var).Format(); 
   GetDlgItem(IDC_EDIT5)->SetWindowText(str); 


//数值字段读取
   var   =m_pRecordset->Fields->GetItem(long(4))->GetValue();; 
   str.Format("%10.2f",   (double)var.fltVal);   
   GetDlgItem(IDC_EDIT6)->SetWindowText(str);  
}

void CDb2Dlg::OnButton1() 
{
  m_pRecordset->MoveNext();//移到下一条记录
  if(m_pRecordset->adoEOF)//如果到了结尾
     m_pRecordset->MoveFirst();   //跳回第1条

  ReadData();  
}

void CDb2Dlg::OnButton4() 
{
 // TODO: Add your control notification handler code here
  m_pRecordset->MoveLast();//移到下一条记录

  ReadData();
}

void CDb2Dlg::OnButton2() 
{
 // TODO: Add your control notification handler code here
  m_pRecordset->MoveFirst();//移到下一条记录
  ReadData();
}

void CDb2Dlg::OnButton3() 
{
 // TODO: Add your control notification handler code here
   m_pRecordset->MovePrevious();//移到下一条记录
  if(m_pRecordset->adoBOF)//如果到了最前
     m_pRecordset->MoveLast();   //跳回最后1条
 
 ReadData();
}

void CDb2Dlg::OnButton5() 
{
 // TODO: Add your control notification handler code here
 GetDlgItem(IDC_EDIT1)->EnableWindow(true); 
 GetDlgItem(IDC_EDIT2)->EnableWindow(true); 
 GetDlgItem(IDC_EDIT3)->EnableWindow(true);

 GetDlgItem(IDC_EDIT1)->SetWindowText(""); 
 GetDlgItem(IDC_EDIT2)->SetWindowText(""); 
 GetDlgItem(IDC_EDIT3)->SetWindowText(""); 
}

void CDb2Dlg::OnButton6() //保存
{
     CString m1,m2,m3,m4,m5;
       GetDlgItem(IDC_EDIT1)->GetWindowText(m1);
       GetDlgItem(IDC_EDIT2)->GetWindowText(m2);
       GetDlgItem(IDC_EDIT3)->GetWindowText(m3);
       GetDlgItem(IDC_EDIT5)->GetWindowText(m4);
       GetDlgItem(IDC_EDIT6)->GetWindowText(m5);

    //判断哪个被选定
    int n;
    n=GetCheckedRadioButton(IDC_RADIO1,IDC_RADIO2);
    CString sx;
    GetDlgItemText(n,sx);   

 
       if (m1=="" )
       {
          AfxMessageBox("学号数据不能为空值!");
          return;
       }
 
    _RecordsetPtr m_pRecordset;
    m_pRecordset.CreateInstance("ADODB.Recordset");
    CString sql;
       _variant_t RecordsAffected;
    sql="select * from stu";
       m_pRecordset->Open(_variant_t(sql),_variant_t((IDispatch*)m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
 
       m_pRecordset->AddNew();
 
  m_pRecordset->PutCollect("xh",_variant_t(m1));

  if (m2!="")//如果不判断是否为空会出错
  m_pRecordset->PutCollect("xm",_variant_t(m2));

  bool x1;
  if (sx=="男")
 x1=1;
  else
    x1=0;
 m_pRecordset->PutCollect("xb",_variant_t(x1));

//日期字段  和字符串一样保存
  m_pRecordset->PutCollect("csny",_variant_t(m4));
  
//保存数值字段
  float x;       
  x=atof(m5);//字符串分数  转  浮点
  m_pRecordset->PutCollect("fs",_variant_t(x));
  
  m_pRecordset->Update();
//  m_pRecordset->Close();
 
 // GetDlgItem(IDC_BUTTON2)->EnableWindow(false);    //保存 "失效"
 // GetDlgItem(IDC_BUTTON3)->EnableWindow(false);    //取消 "失效"
 
  GetDlgItem(IDC_EDIT1)->EnableWindow(false);   
 
 if (LinkTable())
 ReadData();//重新装载数据
ReadData2ListView();
}


void CDb2Dlg::OnButton7() 
{
 // TODO: Add your control notification handler code here
 m_pRecordset->Delete(adAffectCurrent);

 GetDlgItem(IDC_EDIT1)->SetWindowText(""); 
 GetDlgItem(IDC_EDIT2)->SetWindowText(""); 
 GetDlgItem(IDC_EDIT3)->SetWindowText("");

 if (LinkTable())
    ReadData();//重新装载数据

 ReadData2ListView();
}

void CDb2Dlg::OnButton8() //修改
{
     CString m1,m2,m3,m4,m5;
//       GetDlgItem(IDC_EDIT1)->GetWindowText(m1);  学号不能修改
       GetDlgItem(IDC_EDIT2)->GetWindowText(m2);
       GetDlgItem(IDC_EDIT3)->GetWindowText(m3);


  if (m2!="")//如果不判断是否为空会出错
  m_pRecordset->PutCollect("xm",_variant_t(m2));

  if (m3!="")
  m_pRecordset->PutCollect("xb",_variant_t(m3));

 
  m_pRecordset->Update();
 
 // GetDlgItem(IDC_BUTTON2)->EnableWindow(false);    //保存 "失效"
 // GetDlgItem(IDC_BUTTON3)->EnableWindow(false);    //取消 "失效"

  ReadData2ListView(); 
}

void CDb2Dlg::OnButton9() //查找
{
 CString  m,m1;
 GetDlgItem(IDC_EDIT4)->GetWindowText(m1);
 bool b;
 b=0;
 m_pRecordset->MoveFirst();
 while (!m_pRecordset->adoEOF)
  {
   m=(char *)(_bstr_t)m_pRecordset->GetCollect("xh");
   if (m==m1)
   {
        b=1;
     break;
   }
   m_pRecordset->MoveNext();
  } 
 if (b)
  {
   ReadData();
  }
 else
  AfxMessageBox("查找不成功!");
}