如何使用ADO连接Mysql数据库

1、下载mysql odbc

2、安装后，采用odbc建立一个连接的mysql数据连接，通过udl文件取出其中的字符串。

3、替换在vc中连接ado的数据的字符串方法，即可正常连接。

采用myodbc的字符串如下：

CString strConn = "Provider=MSDASQL.1Password=zcc123Persist Security Info=True/

User ID=rootData Source=test"

由于ADO对象不直接支持MySQL,所以必须先安装MyODBC, 后者也是一个免费产品，在 www.mysql.org上有下载，安装好了MyODBC, 就可以在ODBC数据源管理中配置一个数据源名称，把它指向你想连接的MySQL数据库。代码如下：Sub connectMySQL() '通过MyODBC去连接MySQL数据库，并将Microsoft SQL Server 7 '的数据转进mysql中 Dim sConnect As String, sSql As String, i As Long Dim cnMSSQL As New ADODB.Connection Dim cnMySQL As New ADODB.Connection'声明并创建对象 连接 Dim rs As New ADODB.Recordset '声明并创建对象 记录集 Dim cm As New ADODB.Command '声明并创建对象 命令sConnect = "dsn=mysql1" '指定MySQL的数据源名称 cnMySQL.Open sConnect '连接到 mysqlsConnect="Provider=SQLOLEDB.1Persist Security Info=FalseUser ID=sapwd=123456Initial Catalog=softdownData Source=ntserver" '连接到 ms sql server 7 cnMSSQL.Open sConnect'sSql = "create table softinfo (softNum smallint,softname varchar(70),softdesc blob," &_ "softpath varchar(30),softleng varchar(10),softclass varchar(10),softsugest tinyint(1)," &_ "softdown smallint(4))" '创建新的MySQL数据表语句 sSql = "select * from softinfo order by softnum" rs.Open sSql, cnMSSQL, 1, 1 While Not rs.EOF sSql = "insert into softinfo values (" &Trim(rs(0).Value) &",'" &Trim(rs(1).Value) &_"','" &Trim(rs(2).Value) &"','" &Trim(rs(3).Value) &"','" &Trim(rs(4).Value) &_"','" &Trim(rs(5).Value) &"'," &Trim(rs(6).Value) &"," &Trim(rs(7).Value) &")" cm.ActiveConnection = cnMySQL cm.CommandType = adCmdText cm.CommandText = sSql cm.Execute rs.MoveNext Wendrs.Close Set rs = NothingcnMySQL.Close Set cnMySQL = NothingcnMSSQL.Close Set cnMSSQL = Nothing End Sub

自己写一个类

#ifndef _acc_data

#define _acc_data

class accdata

{

public:

accdata()

virtual~accdata()

_ConnectionPtr m_pConnection

_RecordsetPtr m_pRecordset

bool open_datas()

bool close_datas()

bool Execute_sql(LPCTSTR str)执行SQL语句

bool list_sql(LPCTSTR str,CListCtrl &m_list)在CListCtrl列表中显示查询的值

}

#endif

.cpp

#include "stdafx.h"

#include "accdatas.h"

#ifdef _DEBUG

#define new DEBUG_NEW

#undef THIS_FILE

static char THIS_FILE[] = __FILE__

#endif

accdata::accdata()

{

}

accdata::~accdata()

{

}

bool accdata::open_datas()

{

bool flag

flag=true

_ConnectionPtr m_pConnection=NULL

try

{

m_pConnection.CreateInstance(__uuidof(Connection))

// 打开本地Access库

m_pConnection->ConnectionTimeout=10

m_pConnection->Open("Provider=Microsoft.Jet.OLEDB.4.0Data Source=你的access数据库名","","",adModeUnknown)

}

catch(_com_error e)

{

AfxMessageBox("数据库连接失败，确认数据库是否在当前路径下!")

flag=false

}

return flag

}

bool accdata::Execute_sql(LPCTSTR str)

{

bool Flag =true

_variant_t RecordsAffected

try

{

m_pConnection.CreateInstance(_uuidof(Connection))

m_pConnection->ConnectionTimeout = 10

m_pConnection->Open("Provider=Microsoft.Jet.OLEDB.4.0Data Source=你的access数据库名","","",adModeUnknown)

m_pConnection->Execute(str,&RecordsAffected,adCmdText)

}

catch(_com_error&e)

{

AfxMessageBox( e.Description(),MB_ICONSTOP )

Flag=false

}

return Flag

}

bool accdata::close_datas()

{

if(m_pConnection->State)

m_pConnection->Close()

m_pConnection= NULL

return true

}

bool accdata::list_sql(LPCTSTR str,CListCtrl &m_list)

{

int i=0

bool flag=true

try

{

m_pConnection.CreateInstance(_uuidof(Connection))

m_pConnection->ConnectionTimeout = 10

m_pConnection->Open("Provider=Microsoft.Jet.OLEDB.4.0Data Source=你的access数据库名","","",adModeUnknown)

m_pRecordset.CreateInstance(_uuidof(Recordset))

m_pRecordset->Open(str,(IDispatch*)m_pConnection,adOpenDynamic,adLockOptimistic,adCmdText)

if(!m_pRecordset->BOF)

{

m_pRecordset->MoveFirst()

while(!m_pRecordset->adoEOF)//遍历所有记录

{

m_list.InsertItem(i,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("字段1"))

m_list.SetItemText(i,1,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("字段2"))

m_list.SetItemText(i,2,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("字段3"))

m_list.SetItemText(i,3,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("字段4"))

m_list.SetItemText(i,4,(LPCTSTR)(_bstr_t)m_pRecordset->GetCollect("字段5"))

i++

m_pRecordset->MoveNext()

}

}

else

{

AfxMessageBox("没有符合条件的数据")

}

}

catch(_com_error e)

{

AfxMessageBox(e.Description())

flag=false

}

m_pRecordset->Close()

m_pConnection->Close()

return flag

}

---