Database establishment and linking issues

              After completing the student management system, after the master’s acceptance, I found that I did not have a deep understanding of the original concept! Then I briefly looked at the 5 examples. The teacher often said that the most important thing in learning is repetition. Through this observation, I regained my understanding of the basic concepts.

First look at a few professional terms: ADO---activex data object dynamic data object through OLE DB (object linking and embed Chinese : Object connection and embedding, which is the bottom-end program interface) to achieve access to different data.

When the master asked me about the objects of ADO, I was confused. It turned out that the connection, recordset, etc. defined in the database we were establishing were all objects of ado. It has a total of 7 objects. The most common ones in this student information management system are the two above, 3 independent objects: recordset, connection, command! There are also 4 subsidiary objects: field, parameter, property, error

##OLE DB consists of 3 parts: data provider, Data consumer, service component. A data provider is a program that provides data similar to SQL Serser or ODBC. The program created by VB is a typical data consumer. It is a data application created using the data provided by the data provider. The service component is responsible for connecting data providers and data consumers.

The five examples use the micrsoft access database created by the visual manager of the vb external program (the file suffix is ​​.mdb), and the student information management system The database is created using SQL Sever (the suffix is ​​.sql). The first step for a database application is to create a database! The second step is to establish the ADO reference. In the VB program, directly click on the reference under the project and select Microsoft ActiveX Data Objects 2.6 Library! The third step is to establish a connection using code. Let's take a look at the comparison of different connection methods to the database:

1. Directly connect to the access database

dim Objcn as new connection,objRs as new recordset
objcn.connectionstring="Prvider=Microsoft.jet.OLEDB.3.5.1;" & _
"data sourse=数据远文件路径"         '连接数据提供者与数据源
objcn.open
       strSQL=“select ……from …… where……”
set objrs.activeconnection=objcn   '将数据库记录集与数据源相匹配
objrs.open(strSQL)      '记录集打开
       .                       ‘进行数据库的增删改查操作
objcn.close         ‘关闭数据库
set objrs=Nothing
set objcn=Nothing

2. Use ODBC to connect to the access database. ODBC has three data source connection methods, namely user DNS, system DNS, file DNS, and student information. It is the addition of file DNS that the management system utilizes.

set objcn=new connection
objcn.open="dsn=数据库文件名"
set objrs=new recordset
                .
objcn.close
set objrs=nothing
set objcn=nothing

3. Use ado data control to create a database connection. This method is relatively simple. Just connect from the control properties.

After reading the connection methods of 5 examples, let’s analyze the code for connecting to the student management system during login:

1. In the login window In order to become more professional, the login name of the database is often set to the system user name, so the system user is automatically obtained in the user name part of the login form, and the API function is called at the beginning:

Private Declare Function GetUsername Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

The form loading part obtains the system user name:

Private Sub Form_Load()
    Dim sbuffer As String
    Dim lsize As Long
    
    sbuffer = Space$(255)  '因无法确定系统用户名的长度，先开辟一个255（最大值）空格字符控件
    lsize = Len(sbuffer)   'lsize盛放sbuffer字符串的真是长度
    Call GetUsername(sbuffer, lsize) 
    'api中字符串作函数，需要提前确定大小
    
    If lsize > 0 Then
        txtusername.Text = Left$(sbuffer, lsize)
    Else
        txtusername.Text = vbNullString
        '没有字符串
    End If
    
    ok = False
    micount = 0  ’用于登陆次数的标记
 End Sub

2. Establish a connection in the confirmation button:

Private Sub cmdOK_Click()
    Dim txtSQL As String
    Dim mrc As ADODB.Recordset '用于保存数据源记录集
    Dim MsgText As String
    
    UserName = ""     '先将登录名滞空
    If Trim(txtusername.Text = "") Then     '确认用户名输入框不为空
        MsgBox "没有这个用户，请重新输入用户", vbOKOnly + vbExclamation, "警告"
        txtusername.SetFocus
    Else
        txtSQL = "select * from user_info where user_id ='" & txtusername.Text & "'"
        Set mrc = ExecuteSQL(txtSQL, MsgText)    '
 调用模块当中的executesql函数
建立数据库连接
        If mrc.EOF Then
            MsgBox "没有这个用
户，请重新输入用户", vbOKOnly + vbExclamation, "警告"
            txtusername.SetFocus
        Else                                    '确认密码
            If Trim(mrc.Fields(1)) = Trim(txtpassword.Text) Then
                ok = True
                mrc.Close
                Me.Hide
                UserName = Trim(txtusername.Text)
                frmmain.Show
            Else
                MsgBox "输入密码不正确，请重新输入！", vbOKOnly + vbExclamation, "警告"
                txtpassword.SetFocus
                txtpassword.Text = ""
            End If
        End If
    End If
    
    micount = micount + 1          '值允许3次输入
    If micount = 3 Then
        Me.Hide
    End If
    Exit Sub
    
End Sub

3. Understand the connection method of executing the excutesql function.

Public Function ConnectString() As String
    ConnectString = "FileDSN=student.dsn;UID=sa;PWD=123" '文件源，登录名，验证密码
End Function
Public Function ExecuteSQL(ByVal SQL As String, MsgString As String) As ADODB.Recordset
   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset
   Dim sTokens() As String
   
   'On Error GoTo ExecuteSQL_Error
   
   sTokens = Split(SQL) '该函数定义将sql语句中的词以空格为分解符分解开放进一个数组里
   Set cnn = New ADODB.Connection '实体化，连接数据源
   cnn.Open ConnectString     '打开数据源
   
   If InStr("INSERT,DELETE,UPDATE", UCase$(sTokens(0))) Then       '比较确定sql数据开始单词是否为增，删，改
      cnn.Execute SQL   '执行SQL语句
      MsgString = sTokens(0) & " query successful"
      '虽然MsgString不是返回值，但传递方式是ByRef，实参地址和这个地址相同
   Else
      Set rst = New ADODB.Recordset    '实例化记录集
      rst.Open Trim$(SQL), cnn, adOpenKeyset, adLockOptimistic   '打开要执行的内容sql，连接数据源，游标类型：键集游标,窗口值固定大小
      '得到临时表，游标指向第一条记录
      'get RecordCount，
      Set ExecuteSQL = rst
      MsgString = "查询到" & rst.RecordCount & _
         " 条记录 "
   End If
   
ExecuteSQL_Exit:  ‘关闭连接
   Set rst = Nothing
   Set cnn = Nothing
   Exit Function
   
ExecuteSQL_Error:
   MsgString = "查询错误: " & _
      Err.Description
   Resume ExecuteSQL_Exit
End Function

# In summary, there is a three-step strategy for establishing a connection: Open--Execute (add, delete, modify, check)- -Close

The above is the content of database establishment and linking issues. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!