VC＋ADO存取SQL SERVER

 VC＋ADO存取SQL SERVER

下面的代码是这样生成的

1)先生成一个MFC 对话框工程(名字叫Test)，并在stdafx.h加入下面一行，(注意路径)

#import "c:/program files/common files/system/ado/msado15.dll" no_namespace rename("EOF","adoEOF")

2)再在Test.h增加下面几个变量(protected)

_ConnectionPtr m_pConnection;//connection object's pointer

_CommandPtr m_pCommand; //command object's pointer

_ParameterPtr m_pParameter; //Parameter object's pointer

_RecordsetPtr m_pRecordset;

3)再修改Test.cpp中的InitInstance()

//初始化COM库

::AfxOleInit();

//连接数据库

HRESULT hr;

try

{

hr=m_pConnection.CreateInstance("ADODB.Connection");

 

if(SUCCEEDED(hr))

{

//如下的语句

char strConnection[100]=" Provider=sqloledb;Data Source=tulip;Initial Catalog=Pubs,User ID=sa; pwd=8818308";

m_pConnection->ConnectionTimeout=10;//User ID=sa; pwd=8818308;

hr=m_pConnection->Open("Provider=sqloledb; Data Source=tulip; Initial Catalog=Pubs","sa","8818308",adModeUnknown);

// hr=m_pConnection->Open("Provider=sqloledb; Server=tulip;DATABASE=Northwind;UID=sa;PWD=8818308","","",adModeUnknown);

// hr=m_pConnection->Open("driver={SQL Server}; Server=127.0.0.1;DATABASE=Northwind;UID=sa;PWD=8818308","","",adModeUnknown);

//得到结果

_variant_t RecordsAffected;

m_pRecordset.CreateInstance("ADODB.Recordset");

m_pRecordset=m_pConnection->Execute("select * from jobs",&RecordsAffected,adCmdText);

while (!m_pRecordset->adoEOF)

{

TRACE("job_id:%d,max_lvl:%d,min_lvl:%d/r/n",m_pRecordset->GetCollect(_variant_t((long)0)),m_pRecordset->GetCollect("max_lvl"),m_pRecordset->GetCollect("max_lvl"));

m_pRecordset->MoveNext();

}

//存放变量

_variant_t job_id,job_desc,max_lvl,min_lvl;

while(!m_pRecordset->adoEOF)

{

job_id=m_pRecordset->GetCollect(_variant_t((long)0));//得到第一项，也可以直接用字段名，见下

job_desc=m_pRecordset->GetCollect("job_desc");

max_lvl=m_pRecordset->GetCollect("max_lvl");

min_lvl=m_pRecordset->GetCollect("min_lvl");

///在DEBUG方式下的OUTPUT窗口输出记录集中的记录

if(job_id.vt != VT_NULL && job_desc.vt != VT_NULL && max_lvl.vt != VT_NULL && max_lvl.vt != VT_NULL)

TRACE("job_id:%d,max_lvl:%d,min_lvl:%d/r/n",job_id.lVal,/*job_desc,*/max_lvl.lVal,min_lvl.lVal);

m_pRecordset->MoveNext();

}

}

if (SUCCEEDED(hr))

{

hr=m_pCommand.CreateInstance("ADODB.Command");

m_pCommand->ActiveConnection=m_pConnection;

// char strQry[100] = "SELECT au_fname, au_lname, address, city "

//"FROM authors WHERE state = ";

// char strQry[100]="Select * from jobs where job_id =";

// strcat(strQry,"'");

// strcat(strQry,"12");

// strcat(strQry,"'");

// m_pCommand->CommandText="select * from jobs where jobs_id =";

m_pCommand->CommandText="Update jobs set job_desc=? where job_id=?";

m_pCommand->CommandType=adCmdText;

m_pParameter=m_pCommand->CreateParameter("job_desc",adVarChar,adParamInput,50,"网络2423423");

m_pCommand->Parameters->Append(m_pParameter);

m_pParameter=m_pCommand->CreateParameter("job_id",adSmallInt,adParamInput,10,"12");

m_pCommand->Parameters->Append(m_pParameter);

_variant_t vNULL;

vNULL.vt = VT_ERROR;

vNULL.scode = DISP_E_PARAMNOTFOUND;///定义为无参数

m_pCommand->Execute(&vNULL,&vNULL,adCmdText);

}

}

catch(_com_error e) //捕捉异常

{

CString errormessage;

errormessage.Format("连接数据库失败!/r/n错误信息:%s",e.ErrorMessage());

AfxMessageBox(errormessage);//显示错误信息

return FALSE;

}

// Standard initialization

// If you are not using these features and wish to reduce the size

// of your final executable, you should remove from the following

// the specific initialization routines you do not need.以下不变

4)运行此程序需要有sql支持，注意用户和密码

其实MSDN有许多有ADO的例子，具体路径是

MSDN->Platform SDK Documentation-->Data Services ->>Microsoft Data Access Components (MDAC)SDK -->Microsoft ActiveX Data Objects (ADO)--->ADO Programmer's Refernce -->ADO API Reference到了这里你就可以看有关ADO的资料了