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)!