用VB程序导入与导出MySQL数据的问题(急)
怎样在VB中编写程序,实现MySQL数据的导入,导出?
谢谢!
[解决办法]
我也正在做这样的一个Project, 用VB.Net和MySQL,暂时使用的是MySQL的存储过程实现数据输入输出,遇到了很大的问题,总是提示subquery 返回值为多行,搞的好多function 都不工作,正痛苦的研究中......
[解决办法]
下载myodbc后安装
示例程序
Option Explicit
Public Function openMYSqlDB(c As Connection, ByVal userName As String, Optional password As String) As Boolean
On Error GoTo errorHandler
Dim p As String
c.Open "DRIVER={MyODBC Driver};SERVER=localhost;PORT=;DATABASE=test;USER= " & userName & ";PASSWORD= " & password
openMYSqlDB = True
Exit Function
errorHandler:
MsgBox Err.Description, vbCritical, "打开数据库错误 "
End
End Function
Private Sub Command1_Click()
Dim Conn As New ADODB.Connection
Dim Rs As Recordset
Dim Sql As String
Dim Rc As String
If openMYSqlDB(Conn, "root ") = True Then
Sql = "select length(name) as l,name,address from mytable where name= ' " & "张三 ' "
Set Rs = Conn.Execute(Sql)
Do While Not Rs.EOF And Not Rs.BOF
Rc = Rc & "l= " & Rs.Fields( "l ").Value & "______ "
Rc = Rc & "Name= " & Rs.Fields( "name ").Value & "______ "
Rc = Rc & " Address= " & Rs.Fields( "address ").Value & vbCrLf
Rs.MoveNext
Loop
Text1.Text = Rc
End If
Rs.Close
Set Rs = Nothing
Conn.Close
Set Conn = Nothing
End Sub
Private Sub Command2_Click()
Dim Conn As New ADODB.Connection
'Dim Rs As Recordset
Dim Sql As String
Dim Rc As String
If openMYSqlDB(Conn, "root ") = True Then
Sql = "insert into mytable(name,address) values( '张三 ', 'address here ') "
Conn.Execute Sql
MsgBox "ok "
End If
'Rs.Close
'Set Rs = Nothing
Conn.Close
Set Conn = Nothing
End Sub
Private Sub Command3_Click()
Dim Conn As New ADODB.Connection
'Dim Rs As Recordset
Dim Sql As String
Dim Rc As String
If openMYSqlDB(Conn, "root ") = True Then
Sql = "create table MyTable(id int(3) auto_increment not null primary key, "
Sql = Sql & "name varchar(50), "
Sql = Sql & "address varchar(100) "
Sql = Sql & ") "
Conn.Execute Sql
MsgBox "Create Done "
End If
'Rs.Close
'Set Rs = Nothing
Conn.Close
Set Conn = Nothing
End Sub
Private Sub Command4_Click()
Dim Conn As New ADODB.Connection
'Dim Rs As Recordset
Dim Sql As String
Dim Rc As String
If openMYSqlDB(Conn, "root ") = True Then
Sql = "drop table mytable "
Conn.Execute Sql
MsgBox "Delete done "
End If
'Rs.Close
'Set Rs = Nothing
Conn.Close
Set Conn = Nothing
End Sub
[解决办法]
你为什么不把路径D:\Program Files\MySQL\MySQL Server 5.0\bin\
写道环境变量PATH里去呢?
我的ＢＡＴ文件 ：
csdn.bat
@echo off
mysqldump -uroot -p123456 --opt -B test2 > c:\\oo.sql
@echo on
oo.sql
的一部分 ：
LOCK TABLES `users_groups` WRITE;
/*!40000 ALTER TABLE `users_groups` DISABLE KEYS */;
INSERT INTO `users_groups` VALUES (11,502),(107,502),(100,503),(110,501),(11
1),(100,501),(102,501),(104,502),(100,502);
/*!40000 ALTER TABLE `users_groups` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2007-09-06 8:14:30
[解决办法]
1，连接库。
2，执行SQL语句。
3，取出结果集。
4，用把结果集写入文件。
