Introduction
This is basically a database class. In this project, we connect to SQL Server 2000. We insert, update, delete and select queries. And also fill the grid without using any data control. In this project, I also use file handling.
Using the Code
First, create the private member of the class. The three members of the class are given below:
Private conConnection As New ADODB.connection
Private cmdCommand As New ADODB.Command
Public rstRecordSet As New ADODB.Recordset
ADODB.connection
First, initialize the adodb connection. This object is used to connect the database.
ADODB.Command
Second, initialize the command. This object is used to execute the database query.
ADODB.Recordset
Third, initialize the Recordset. This object is for multiple purposes. I will explain later.
Public Sub connection()
On Error GoTo err1:
Dim txtserver As String
Dim txtuser As String
Dim txtpassword As String
Dim Filename As String
Filename = "c:\Label.txt"
Open (Filename) For Input As #1
Line Input #1, txtserver
Line Input #1, txtuser
Line Input #1, txtpassword
Close #1
conConnection.ConnectionString = "Driver={SQL Server};
Server=" & txtserver & ";Database=Stock_Management;Uid=" & txtuser & ";Pwd=;"
Exit Sub
err1:
Close #1
Exit Sub
End Sub
This function establishes the connection with SQL Server 2000. This is a trusted connection, but it is also used for non-trusted connection. It is a user friendly connection. Therefore, here, I use file handling, it just opens the file and reads server name, user name, and password.
For this purpose, I declare the three variables, txtserver, txtuser, txtpassword. First, open object, open the file for reading not writing. Line Input #1 that reads the line number one of the file and the text into the txtserver. And same takes action on the next two lines. and saves value on next two variables, txtuser and txtpassword and close object closes the file. And then, connect to the SQL Server and exit the function.
Public Function conopen() As Boolean
On Error GoTo err1:
If conConnection.State = 0 Then
connection
conConnection.Open
conopen = True
End If
err1:
If Error <> "" Then
MsgBox "Connect to fail"
conclose
Exit Function
End If
End Function
This function first check the state of the connection if connection is open the leave the without any action. Other wise if connection is close then open the connection and return true.
Note: For checking the connection state, I use connection.State function. If state is one that mean connection is open if state is zero its mean connection is close.
Public Sub insert_query(query As String)
conopen
With cmdCommand
.ActiveConnection = conConnection.ConnectionString
.CommandText = query
.CommandType = adCmdText
.Execute (query)
End With
End Sub
This function take a query. Conopen is a function that I am already explain the working of this function. Next it action with the command object. First cmdCommand variable active the connection then it take the query. adCmdText pass to the cmdCommand.CommandType. adCmdText Evaluate as a textual definition. And cmdCommand.execute any query like insert, select, delete and update.
Public Function rstRecordSetOpen()
Call rstRecordSetclose
If rstRecordSet.State = 0 Then
With rstRecordSet
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmdCommand
End With
End If
End Function
This function open RecordSet object where we populate the data. First, it call the rstRecordSetclose function that closes the RecordSet Object. It is not necessary to close the RecordSet. But sometimes, it creates problems so therefore every time we populate the data, we must close the RecordSet. If you want to more learn about the Recordset, please visit the site.
Function fillcombo(ByRef combo As ComboBox, query As String, field As Integer)
Call conopen
With cmdCommand
.ActiveConnection = conConnection.ConnectionString
.CommandText = query
.CommandType = adCmdText
End With
Call rstRecordSetOpen
If rstRecordSet.EOF = False Then
rstRecordSet.MoveFirst
combo.Clear
Do
combo.AddItem (rstRecordSet.Fields(field))
rstRecordSet.MoveNext
Loop Until rstRecordSet.EOF = True
End If
End Function
This function fills the combobox. This function takes three parameters.
- Take a combo box name
- Take a query
- Take a specified, e.g., you write a query like this
"Select user_id, User_name from user_table". and you want that all the names show in the combo box, then you pass parameter 1, and after completion, it exits the function.
Function getID(query As String, field As Integer) As Integer
Call conopen
With cmdCommand
.ActiveConnection = conConnection.ConnectionString
.CommandText = query
.CommandType = adCmdText
End With
Call rstRecordSetOpen
If rstRecordSet.EOF = False Then
rstRecordSet.MoveFirst
Dim a As Integer
a = field
Do
a = rstRecordSet.Fields(field)
rstRecordSet.MoveNext
Loop Until rstRecordSet.EOF = True
End If
getID = a
End Function
And if you want to get Id of any query, then you call this:
Public Function rstRecordSetclose()
Set rstRecordSet = Nothing
End Function
For closing the recordset, we use call this function:
Public Function conclose()
Set conConnection = Nothing
Set cmdCommand = Nothing
End Function
This function closes the command as well as connection.
Here are some screen shots to using this object.
Here, we create an object and build a connection. I show the code here how the object is working.
Private Sub cmdOK_Click()
Dim a As String
Dim database As New clsDatabase
check
If LoginSucceeded = False Then
Exit Sub
Else
If database.conopen = True Then
a = "SELECT * from tbl_user where (
user_name = '" & txtUserName.Text & "') and (
user_password = '" & txtPassword.Text & "')"
If database.getID(a, 0) <> 0 Then
LoginSucceeded = True
MsgBox "logon sucess full"
End
Else
MsgBox "Invalid Password, try again!", , "Login"
txtPassword.SetFocus
SendKeys "{Home}+{End}"
End If
Else
Unload Me
Set database = Nothing
frmDatabase.Show
Exit Sub
End If
End If
End Sub
This is a cmdOK button action. When we click it, it creates a new object in the name of DataBase. First, we call a conOpen function. If connection is open, then we execute the query for getting of a particular row. For this purpose, we call the getID function. The working of this function is that first, it executes the query and then returns the ID of particular row. And prompts the message log on successfully. Here is the screen shot of it.
If conConnection returns false, there are two, the user name or password is invalid or the connection is not build. And prompt a message connect to fail. Like here.
If database object fails to connect the SQL Server 2000. Then prompt the message connect to fail. When you click OK, then a window appears on the screen. Where you see the three text boxes, in the first, that has a label server name detect automatically the server name of your SQL Server 2000, like here.
Then you put the user name and password if have, otherwise you leave blank and click Test button.
Private Sub btntest_Click()
checkfile
data
If database.conopen = True Then
MsgBox "Connect to successfuly"
Unload Me
frmLogin.Show
Else
MsgBox "Connection fail"
End If
End Sub
When you click Test button. Then first, it goes to the function checkfile.
Function checkfile()
Dim fName As String
fName = "c:\Label.txt"
Dim oFSO As New FileSystemObject
On Error GoTo ErrorHandler
oFSO.DeleteFile (fName)
ErrorHandler:
On Error Resume Next
Set oFSO = Nothing
End Function
On checkfile function, first it declares a variable in the name of fName. fName takes a path of the file. Then we declare a object FileSystemObject in the name of oSFO. It only does one work that deletes the file. Then, it goes back in the btnTest click event. And then, btntest click event calls another function Data. We look at the following working of data function.
Function data()
On Error GoTo err1
Open "c:\Label.txt" For Append As #1
Print #1, Me.txtservername
Print #1, Me.txtusername
Print #1, Me.txtpassword
Close #1
If Error <> "" Then
err1:
Close #1
data
Exit Function
End If
End Function
Note: detail of Open
Main function of OPEN is to associate a file number (filenum&) with a file or physical device and to prepare that device for reading and/or writing. This file number is then used, rather than its name, in every statement that refers to the file. The FREEFILE function can be used to determine the next available file number, or you can pick one yourself. The OPEN statement contains information on the mode of the file; that is, the methods by which the file will be accessed: sequential (for input/output to a new file, or output to an existing file), random access, and binary. An OPEN statement is usually balanced by a matching CLOSE statement.
Open function opens the file if file does not exist, then create a new file. Above, we have declared three variables that take server name, user name and password. The print function writes these values into the file and Close function closes the file.
That is a little bit of an overview of the object of clsDataBase.
Important Note: If you run this code, first attach the database into your SQL Server that I have provided you.
If you have any problems with this topic, please leave a message in the Comments section below.
