更新数据库中信息 - 数据库编程

方式一：使用SQL语句

更新一列

            strCmd.Format(_T("update classInfo set 班级ID=%d where ID=%d"),
                iClassID,
                iClassID);
            spCmd->CommandText=(LPCTSTR)strCmd;
            spCmd->Execute(NULL,NULL,adCmdText);

更新多列用,号隔开

UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'
WHERE LastName = 'Wilson'

批量更新多项

    try{
        CString strCmd;
        _CommandPtr spCmd;
        spCmd.CreateInstance(__uuidof(Command));
        spCmd->ActiveConnection=m_pConnection;
        int nCount=m_BanjiList.GetItemCount();
        CString ruxuenianStr;
        CString banjiStr;
        CString xueqiStr;
        for (int nIndex=0;nIndexCommandText=(LPCTSTR)strCmd;
            spCmd->Execute(NULL,NULL,adCmdText);
        }
    }
    catch(_com_error &e)
    {
        AfxMessageBox(e.Error ());
    }

    //批量更新班级表中的学号
    CString strCmd;
    strCmd.Format(
        _T("SELECT * FROM 班级表%d order by 学号"),
        m_pParentStudentInfo->m_nClassID);
    pRecordSet->Open((_variant_t)strCmd,m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
    {
        pRecordSet->MoveLast();
        int nNew=0;
        while (!pRecordSet->adoBOF)
        {
            varValue=pRecordSet->GetCollect(_T("学号"));
            if (varValue.vt!=VT_NULL)
            {
                nStuXuehao=_ttoi(((LPCTSTR)_bstr_t(varValue)));
                //nNew=id_Map[nStuXuehao];    
                pRecordSet->PutCollect(_T("学号"),(long)-nStuXuehao);
                pRecordSet->MovePrevious();
            }        
        }
        pRecordSet->UpdateBatch(adAffectAll);
    }

方式二：使用AddNew Update

        _RecordsetPtr pRecordSet;
         CString strCmd;
        pRecordSet.CreateInstance(__uuidof(Recordset));
        strCmd.Format(
            _T("SELECT * FROM 班级表%d where 学号=%d"),    \\先定位要修改的那条记录
            m_pParentStudentInfo->m_nClassID,
            nXuehao);
            
            
         pRecordSet->Open((_variant_t)strCmd,m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
    //    pRecordSet->AddNew();  添加新记录 使用AddNew, 修改记录，则删除此语句
    
    
        pRecordSet->PutCollect(_T("学号"),(long)nXuehao);
        pRecordSet->PutCollect(_T("姓名"),(LPCTSTR)nameStr);
        pRecordSet->PutCollect(_T("性别"),(LPCTSTR)sexStr);
        pRecordSet->PutCollect(_T("地址"),(LPCTSTR)addStr);
        pRecordSet->PutCollect(_T("联系方式"),(LPCTSTR)lianxiStr);
        pRecordSet->PutCollect(_T("备注"),(LPCTSTR)beizhuStr);
        CFile f;
        CFileException e;
        if(f.Open(zhaoPianStr, CFile::modeRead | CFile::typeBinary, &e)) //打开了一个jpg文件
        {    
            int nSize = f.GetLength();          //先得到jpg文件长度
            BYTE * pBuffer = new BYTE [nSize];  //按文件的大小在堆上申请一块内存
            if (f.Read(pBuffer, nSize) > 0 )    //把jpg文件读到pBuffer(堆上申请一块内存)
            { 
                BYTE *pBuf = pBuffer;     ///下面这一大段是把pBuffer里的jpg数据放到库中
                VARIANT            varBLOB;
                SAFEARRAY        *psa;
                SAFEARRAYBOUND    rgsabound[1];
                if(pBuf)
                {    
                    rgsabound[0].lLbound = 0;
                    rgsabound[0].cElements = nSize;
                    psa = SafeArrayCreate(VT_UI1, 1, rgsabound);
                    for (long i = 0; i < (long)nSize; i++)
                        SafeArrayPutElement (psa, &i, pBuf++);
                    varBLOB.vt = VT_ARRAY | VT_UI1;
                    varBLOB.parray = psa;
                    pRecordSet->GetFields()->GetItem(_T("照片"))->AppendChunk(varBLOB);
                }
                delete []pBuffer;
                pBuf=NULL;
            }
            f.Close ();
        }
        pRecordSet->Update();//更新此记录
        pRecordSet->Close ();
        //将此学生更新到课程表中
        strCmd.Format(
            _T("SELECT * FROM 课堂成绩表%d where 学号=%d"),
            m_pParentStudentInfo->m_nXueqiID,
            nXuehao);
        pRecordSet->Open((_variant_t)strCmd,m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
        pRecordSet->PutCollect(_T("学号"),(long)nXuehao);
        pRecordSet->PutCollect(_T("姓名"),(LPCTSTR)nameStr);
        pRecordSet->Update();
            _RecordsetPtr pRecordSet;
            CString strCmd;
            pRecordSet.Crea

更新数据库中信息(一)