VB连接SQL问题

twtdn 2009-06-26 02:38:31

Dim MyConStr As String '连数据库字符串
Dim sqlstr As String, conn2 As New ADODB.Connection '连接对象
MyConStr = "dsn=test.;UID=sa;Pwd=123456;"
Dim rs As New ADODB.Recordset

conn2.Open MyConStr
For i = 0 To 11
sqlstr = "select * from khgl where 1=1 and " & str_sel(i)
rs.Open sqlstr, conn2, 1, 1
arrData(i, 2) = rs.RecordCount
rs.Close
Next i

With MSChart1
.RowCount = 12
.ChartData = arrData
.Column = 1
.ColumnLabel = "产量(台)"
.Refresh
End With
Set rs = Nothing
Set conn2 = Nothing
小弟采用DSN连接数据库，但是连接不上，请高手帮我采用代码访问数据库，服务器=IT，数据库名=khgl,用户名=sa，密码=123456

...全文

39 2 打赏收藏转发到动态举报

写回复

用AI写文章

2 条回复

切换为时间正序

请发表友善的回复…

发表回复

q149475844 2009-06-30

打赏
举报

Public conn As ADODB.Connection
Public rs As ADODB.Recordset
Public re As ADODB.Recordset
Public addFlag As Boolean '声明部分
Public Function OpenCn(ByVal Cip As String, ByVal users As String, ByVal pw As String) As Boolean '连接模块填写数据库等信息
Dim mag As String
On Error GoTo strerrmag
Set conn = New ADODB.Connection
conn.ConnectionTimeout = 25
conn.Provider = "sqloledb"
conn.Properties("data source").Value = Cip '服务器的名字
conn.Properties("initial catalog").Value = "Kmoney" '库名
'conn.Properties("integrated security").Value = "SSPI" '登陆类型
conn.Properties("user id").Value = users 'SQL库名
conn.Properties("password").Value = pw '密码
'sql = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;password=;Initial Catalog=pubs;Data Source=127.0.0.1" '如果不用这个模块也行可以，这一句便是常用的引擎。
'conn.ConnectionString = sql
conn.Open
OpenCn = True
If conn.State = 1 Then addFlag = True
Exit Function
strerrmag:
mag = "连接错误"
Call MsgBox(mag, vbOKOnly, "Error:Data connect")
addFlag = False
Exit Function '连接错误消息
End Function

'关闭数据库，释放连接
Public Sub cloCn()
On Error Resume Next
If conn.State <> adStateClosed Then conn.Close
Set conn = Nothing
End Sub

Public Function openRs(ByVal strsql As String) As Boolean '连接数据库记录集
Dim mag As String
On Error GoTo strerrmag
Set rs = New ADODB.Recordset
'rs.Open strsql, conn, 1, 1
With rs
.ActiveConnection = conn
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open strsql
End With
addFlag = True
openRs = True
'End '将记录集给rs
Exit Function
strerrmag:
mag = "错"
Call MsgBox(mag, vbOKOnly, "error:connect")
openRs = False
End
'Exit Function '连接错误消息
End Function
Public Sub cloRs()
On Error Resume Next
If rs.State <> adStateClosed Then rs.Clone
Set rs = Nothing '释放记录集
End Sub

把以上的代码放到模块中
Call OpenCn("(local)", "sa", "") '这个是调用接口,如果是外网的话(local)这个要改成IP，本机就不用改了
Call openRs(这里写SQL语句)

还要在工程部件里调用microsoft ADO Data Control 6.0 (SP6)这个控件，将他放入窗体，这样就行了

我也是个新手，希望能加我QQ，一起学习VB
QQ：149475844

饮水需思源 2009-06-27

打赏
举报

dim conn as new adodb.connection

With conn

    If .State = adStateOpen Then .Close

    .ConnectionString = "driver=SQL Server;server=计算机名或IP地址;uid=sa;pwd=;database=数据库名"

    .CommandTimeout = 0

    .Open

End With