sql server + vb 6.0 [英] sql server + vb 6.0

问题描述

我在 vb 6.0 中创建了一个数据库项目.我在 sql server 2000 中创建了我的数据库.我使用 adodc 和 odbc 连接到数据库.

I have created one database project in vb 6.0. I have created my database in sql server 2000. I am using adodc and odbc to connect to database.

我想将我的 sql server 保留在一个系统中，并将我的 vb 6.0 应用程序保留在另一个系统中，并且我想从网络访问 sql 数据库.

I want to keep my sql server in one system and my vb 6.0 application in another system and I want to access sql database from network.

请帮帮我.谢谢.

推荐答案

这里是类，cDatabase，我用来通过 ODBC DSN 连接访问 MS SQL 数据库:

Here is the Class, cDatabase, I use to use to access a MS SQL database using an ODBC DSN Connection:

Option Explicit

Private m_eDBCursorType As ADODB.CursorTypeEnum  'Cursor (Dynamic, Forward Only, Keyset, Static)
Private m_eDBLockType As ADODB.LockTypeEnum    'Locks (BatchOptimistic,Optimistic,Pessimistic, Read Only)
Private m_eDBOptions As ADODB.CommandTypeEnum 'DB Options
Private m_sDSNName As String
Private m_sSQLUserID As String
Private m_sSQLPassword As String
Private cn As ADODB.Connection

Private Sub Class_Initialize()
    m_eDBCursorType = adOpenForwardOnly
    m_eDBLockType = adLockReadOnly
    m_eDBOptions = adCmdText
End Sub

Private Function ConnectionString() As String

    ConnectionString = "DSN=" & m_sDSNName & "" & _
                            ";UID=" & m_sSQLUserID & _
                            ";PWD=" & m_sSQLPassword & ";"

End Function

Private Sub GetCN()
On Error GoTo GetCN_Error

If cn.State = 0 Then
StartCN:
    Set cn = New ADODB.Connection
    cn.Open ConnectionString

    With cn
        .CommandTimeout = 0
        .CursorLocation = adUseClient
    End With
End If


On Error GoTo 0
Exit Sub

GetCN_Error:
If Err.Number = 91 Then
    Resume StartCN
Else
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure GetCN of Module modDatabaseConnections"
End If

End Sub

Public Function GetRS(sSQL As String) As ADODB.Recordset

Dim eRS As ADODB.Recordset

On Error GoTo GetRS_Error

TryAgain:

If Len(Trim(sSQL)) > 0 Then
    Call GetCN

    Set eRS = New ADODB.Recordset       'Creates record set
    eRS.Open sSQL, cn, m_eDBCursorType, m_eDBLockType, m_eDBOptions
    Set GetRS = eRS

Else
    MsgBox "You have to submit a SQL String"
End If

On Error GoTo 0
Exit Function

GetRS_Error:
If Err.Number = 91 Then
    Call GetCN
    GoTo TryAgain
ElseIf Err.Number = -2147217900 Then
    Exit Function
Else
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure GetRS of Module" & vbCrLf & vbCrLf & "SQL - " & sSQL
End If

End Function

Public Property Get DBOptions() As ADODB.CommandTypeEnum

    DBOptions = m_eDBOptions

End Property

Public Property Let DBOptions(ByVal eDBOptions As ADODB.CommandTypeEnum)

    m_eDBOptions = eDBOptions

End Property

Public Property Get DBCursorType() As ADODB.CursorTypeEnum

    DBCursorType = m_eDBCursorType

End Property

Public Property Let DBCursorType(ByVal eDBCursorType As ADODB.CursorTypeEnum)

    m_eDBCursorType = eDBCursorType

End Property

Public Property Get DBLockType() As ADODB.LockTypeEnum

    DBLockType = m_eDBLockType

End Property

Public Property Let DBLockType(ByVal eDBLockType As ADODB.LockTypeEnum)

    m_eDBLockType = eDBLockType

End Property

Public Property Get DSNName() As String

    DSNName = m_sDSNName

End Property

Public Property Let DSNName(ByVal sDSNName As String)

    m_sDSNName = sDSNName

End Property

Public Property Get SQLUserID() As String

    SQLUserID = m_sSQLUserID

End Property

Public Property Let SQLUserID(ByVal sSQLUserID As String)

    m_sSQLUserID = sSQLUserID

End Property

Public Property Get SQLPassword() As String

    SQLPassword = m_sSQLPassword

End Property

Public Property Let SQLPassword(ByVal sSQLPassword As String)

    m_sSQLPassword = sSQLPassword

End Property

这是我实例化和调用它的方式:

and here is how I instantiated and called it:

Private Sub Command1_Click()
Dim rs As ADODB.Recordset
Dim DB As cDatabase
Dim l As Long

Set rs = New ADODB.Recordset
Set DB = New cDatabase

    With DB
        .DBCursorType = adOpenForwardOnly
        .DBLockType = adLockReadOnly
        .DBOptions = adCmdText
        .DSNName = "Your_DSN_Name"
        .SQLUserID = "Your_SQL_Login_Name"
        .SQLPassword = "Your_SQL_Login_Password"
        Set rs = .GetRS("Select Field1 FROM Table1")
    End With

    If rs.RecordCount <= 0 Then Goto Exit_Sub

    For l = 1 To rs.RecordCount
        Debug.Print rs(0).Value
        rs.MoveNext
    Next l

Exit_Sub:
rs.Close
Set rs = Nothing
Set DB = Nothing
End Sub

这篇关于sql server + vb 6.0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！