Click here to Skip to main content
Click here to Skip to main content

Data Base Class ADODB Command Recordset vb6 ADO FILE HANDLING OPEN

, 18 Nov 2007
Rate this:
Please Sign up or sign in to vote.
It is a object of data base. where we connect the data sql server and do working in sqlserver.

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 with out 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 member of the class.

        Private conConnection As New ADODB.connection
        Private cmdCommand As New ADODB.Command
        Public rstRecordSet As New ADODB.Recordset

1. ADODB.connection

First initialize the adodb connection. This object use to connect the data base.

2. ADODB.Command

Second initialize the command. This object to execute the data base query.

3. ADODB.Recordset

Third initialize the REcordset. This object for multiple purpose. 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 establish the connection with sql server 2000. This is trusted connection. but it is also use for non-trusted connection. It is user friendly connection. Therefor here i use file handling it just open the file and read server name, user name, and password.

For this purpose i declare the three variable txtserver, txtuser, txtpassword. First open object open the file for reading not writing. Line Input #1 that read the line no. one of the file and the text into the txtserver. And same take action next two lines. and save value on next two variable txtuser and txtpassword. and close object close the file. And then connect to the sql server and exit the function.

<code>

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 the 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 close the RecordSet Object. It is not necessary to close the RecordSet. But some time it is create problems so therefor every we populate the data we must close the RecordSet. If you want to more learn about the Rcordset 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 fill the combo box. This function take three parameter.

1. Take a combo box name

2. Take a query

3. Take a specified. e.g you right a query like this "Select user_id, User_name from user_table". and you want to that all the name is show in the combo box then you pass parameter 1. and after complete its it exit 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 u 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 close the command and as well as connection.

Here are the some screen shot to using this object.

Screenshot - Logon.jpg

Here we create a object and build a connection. i show the code here how the object 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 a a cmdOK button action. When we click it. It create 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 for of particular row. For this purpose we call the getID function. The working of this function is that first it execute the query and then return the ID of particular row. And prompt the message log on successfully. Here is the screen shot of it.

Screenshot - Logon_success.jpg

If conConnection is return 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.

Screenshot - Logon_fail.jpg

If database object fail to connect the sql server 2000. Then prompt the message connect to fail. when you click OK then a window appear on the screen. Where you see the three text box. In first that has a label server name detect automatically the server name of your sql server 2000. like here.

Screenshot - Connect_data_base.jpg

Then you put the user name and password if have other wise 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 he go 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 declare a variable in the name of fName. fName take a path of the file. Then we declare a object FileSystemObject in the name of oSFO. It only one work that delete the file. then it go back in the btnTest click event. And then btntest click event call a another function Data. We look the following the 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 open the file if file is not exit then create a new file. Above we have declare three variable that take server name, user name and <code>password. The print function write these values into the file and Close function close the file.

That is little bit overview the object of clsDataBase.

Important Note: If you run this code first attach the data base into your sql server. That i am provided you.

If you feel any problem please e_mail me. My e-mail address is wise_boy620@hotmail.com

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

M Saqib Ali
Web Developer
Pakistan Pakistan
No Biography provided

Comments and Discussions

 
GeneralMy vote of 1 PinmemberLounge Lizard16-Apr-09 9:50 
GeneralRe: My vote of 1 PinmemberM Saqib Ali22-Dec-09 1:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140709.1 | Last Updated 18 Nov 2007
Article Copyright 2007 by M Saqib Ali
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid