1. Articles
  2. How to create query form in vb6?

How to create query form in vb6?

Submitted by kevern010101 on
Here in this discussion we will be creating a very simple query form in VB6. this will help you have a very quick query on you data inside you own system. You don't have to open you MySql query manager ang query data there. Anyway, let's proceed.... ----------------------------------------------------------------------------------------------------------------------- Author: Kevern Copyright: © 2009 Kevern Solutions
  1. On Form Load:
  2. Private Sub Form_Load()
  3.     If drvrTXT.Text = "" Then
  4.         drvrTXT.Text = "MySQL ODBC 5.1 Driver"
  5.     End If
  6.     configLBL.caption = "[Default Config]" & vbNewLine _
  7.                         & "DRIVER=" & ReadIniValue(App.path & "\Config.ini", "DEFAULT", "DRIVER") & vbNewLine _
  8.                         & "HOST=" & ReadIniValue(App.path & "\Config.ini", "DEFAULT", "HOST") & vbNewLine _
  9.                         & "DBNAME=" & ReadIniValue(App.path & "\Config.ini", "DEFAULT", "DBNAME") & vbNewLine _
  10.                         & "UID=" & ReadIniValue(App.path & "\Config.ini", "DEFAULT", "UID") & vbNewLine _
  11.                         & "PWD=" & ReadIniValue(App.path & "\Config.ini", "DEFAULT", "PWD")
  12.    
  13. End Sub
***Connect Button Code***
  1. Private Sub testCMD_Click()
  2.     Call DBConnect.testconnectDB ---> [Below is the DBConnect Codes]
  3. End Sub
***DBConnect Codes (Module File)***
  1. Option Explicit
  2.     Dim conn As New ADODB.Connection
  3.     Dim rs As New ADODB.Recordset
  4.     Dim TempUserLog As String
  5.  
  6. Public Function testconnectDB()
  7.     Dim conn As ADODB.Connection
  8.     Set conn = New ADODB.Connection
  9.         conn.connectionString = ConnectionStringModule.connectionString
  10.             With conn
  11.                 On Error GoTo errorhandler
  12.                 .Open
  13.                 MsgBox "Connection Successful", vbInformation + vbOKOnly, "Admin: DBConnect Module - testconnectDB"
  14.                 .Close
  15.             End With
  16.         Exit Function
  17. errorhandler:
  18.     MsgBox Err.Description, vbCritical, "Admin: DBConnect Module - testconnectDB"
  19. End Function
  20. Public Sub Login()
  21.     Dim conn As ADODB.Connection
  22.     Set conn = New ADODB.Connection
  23.    
  24.     conn.connectionString = ConnectionStringModule.connectionString
  25.    
  26.     With conn
  27.         On Error GoTo errorhandler
  28.         .Open
  29.             With rs
  30.                 .Open ("Select * from user"), conn, adOpenDynamic, adLockOptimistic
  31.                     .MoveFirst
  32.                     .Find "uname='" & frmLogIn.txtUserName & "'"
  33.                     If .EOF Then
  34.                         If frmLogIn.txtUserName = "admin" And frmLogIn.txtPassword = "@dm1n" Then
  35.                             TempUserLog = frmLogIn.txtUserName
  36.                                 With [You Menu Validate]
  37.                                     . [You Menu Validate](0).Enabled = True
  38.                                     . [You Menu Validate](1).Enabled = True
  39.                                     . [You Menu Validate](2).Enabled = True
  40.                                     . [You Menu Validate](3).Enabled = True
  41.                                     . [You Menu Validate](4).Enabled = True
  42.                                     . [You Menu Validate](5).Enabled = True
  43.                                     . [You Menu Validate](6).Enabled = True
  44.                                     . [You Menu Validate](7).Enabled = True
  45.                                 End With
  46.                             .Close
  47.                             conn.Close
  48.                              [You Menu Validate].Enabled = True
  49.                             Unload frmLogIn
  50.                            
  51.                             Exit Sub
  52.                         Else
  53.                             '===============================
  54.                             frmLogIn.lblAccess = "Access Denied"
  55.                             MsgBox "Invalid Username", vbInformation + vbOKOnly, "Admin: DBConnect Module - Login"
  56.                             frmLogIn.txtUserName.SetFocus
  57.                             SendKeys "{Home}+{End}"
  58.                            
  59.                         End If
  60.                     Else
  61.                         .Find "password='" & frmLogIn.txtPassword & "'"
  62.                         If .EOF Then
  63.                             '================================
  64.                             frmLogIn.lblAccess = "Access Denied"
  65.                             MsgBox "Invalid Password", vbInformation + vbOKOnly, "MCRP Admin: DBConnect Module - Login"
  66.                             frmLogIn.txtPassword.SetFocus
  67.                             SendKeys "{Home}+{End}"
  68.                         Else
  69.                             .Find "inactive='" & 0 & "'"
  70.                             If .EOF Then
  71.                                 MsgBox "Acount Status: Inactive", vbInformation + vbOKOnly, "Admin: DBConnect Module - Login"
  72.                             Else
  73.                                 '====================================
  74.                                 frmLogIn.lblAccess = "Access Denied"
  75.                                 'MsgBox "Acount Status: Active", vbInformation + vbOKOnly, "Admin: DBConnect Module - Login"
  76.                                 TempUserLog = frmLogIn.txtUserName
  77.                                 Unload frmLogIn
  78.                                 frmTip.Show
  79.                             End If
  80.                         End If
  81.                     End If
  82.                 .Close
  83.             End With
  84.         .Close
  85.     End With
  86.     Exit Sub
  87. errorhandler:
  88.         MsgBox Err.Description, vbInformation + vbOKOnly, "Admin: DBConnect Module - Login: Error"
  89. End Sub
  90. Public Static Function UserLog() As String
  91.     UserLog = TempUserLog
  92. End Function
***Execute Button Code*** Note: this code requires Config.ini file in your system (My own idea, customize it to fit your's)
  1. Private Sub execCMD_Click()
  2.     If MsgBox("Overwriting default configuration is not recommended." & vbNewLine _
  3.         & "             Would you like to continue anyway?", vbCritical + vbOKCancel, "MCRP ADMIN") = vbOK Then
  4.         WriteIniValue App.path & "\Config.ini", "DEFAULT", "DRIVER", drvrTXT.Text
  5.         WriteIniValue App.path & "\Config.ini", "DEFAULT", "HOST", hostTXT.Text
  6.         WriteIniValue App.path & "\Config.ini", "DEFAULT", "DBNAME", dbnameTXT.Text
  7.         WriteIniValue App.path & "\Config.ini", "DEFAULT", "UID", uidTXT.Text
  8.         WriteIniValue App.path & "\Config.ini", "DEFAULT", "PWD", pwdTXT.Text
  9.            
  10.         configLBL.caption = "[Default Config]" & vbNewLine _
  11.                         & "DRIVER=" & ReadIniValue(App.path & "\Config.ini", "DEFAULT", "DRIVER") & vbNewLine _
  12.                         & "HOST=" & ReadIniValue(App.path & "\Config.ini", "DEFAULT", "HOST") & vbNewLine _
  13.                         & "DBNAME=" & ReadIniValue(App.path & "\Config.ini", "DEFAULT", "DBNAME") & vbNewLine _
  14.                         & "UID=" & ReadIniValue(App.path & "\Config.ini", "DEFAULT", "UID") & vbNewLine _
  15.                         & "PWD=" & ReadIniValue(App.path & "\Config.ini", "DEFAULT", "PWD")
  16.     End If
  17.    
  18.     drvrTXT.Text = ""
  19.     hostTXT.Text = ""
  20.     dbnameTXT.Text = ""
  21.     uidTXT.Text = ""
  22.     pwdTXT.Text = ""
  23.    
  24. End Sub
***Start Query Button***
  1. Private Sub cmdExec_Click()
  2.     Dim sqlconn As New ADODB.Recordset
  3.     Dim ctrl As Integer
  4.     Dim ctrl1 As Double
  5.     Call ConnModule.ConnOpen
  6.         With sqlconn
  7.                 On Error GoTo errorhandler:
  8.             If .State = adStateClosed Then
  9.                 .Open txtquery.Text, ConnModule.conn, adOpenDynamic, adLockOptimistic
  10.             End If
  11.             On Error GoTo errorhandler:
  12.                 MsgBox "Total Rows Affected... " & .RecordCount, vbInformation + vbOKOnly, "MCRP - SQL Query Utility"
  13.         End With
  14.     Set MSHFlexGrid1.DataSource = sqlconn
  15.    
  16.     ctrl1 = MSHFlexGrid1.Width / MSHFlexGrid1.Cols
  17.     If ctrl1 < 1200 Then
  18.         ctrl1 = 1200
  19.     End If
  20.     For ctrl = 0 To MSHFlexGrid1.Cols
  21.         MSHFlexGrid1.ColWidth(ctrl) = ctrl1
  22.     Next ctrl
  23.    
  24.     Exit Sub
  25. errorhandler:
  26.     MsgBox Err.Description
  27. End Sub