SQL Login form

[Pozzi]

I've created a login form for users to log into a database.

The User ID's & Passwords are stored within the DB. On starting my application I want to connect and open the database, then when the user enters the details carry out the nessary look-up.

Here's what I've done so far...

Option Explicit
    Dim adoCnn As New ADODB.Connection
 
Private Sub cmdCancel_Click()
 
    '* User cancelled, close application.
    Unload Me
    
End Sub
 
Private Sub cmdOK_Click()
 
    Dim adoRstOps As ADODB.Recordset
 
    Dim strUser As String
    Dim strPass As String
    Dim strSQL As String
    
    '* Check that user has entered numeric values.
    If Not IsNumeric(txtUser.Text) Then
        '* Not all numeric, prompt user.
        MsgBox "User ID must be numeric.", vbOKOnly + vbInformation, "Invalid User ID"
        txtUser.Text = ""
        txtUser.SetFocus
        Exit Sub
    End If
    
    If Len(txtPassword.Text) > 0 Then
        If Not IsNumeric(txtPassword.Text) Then
            '* Not all numeric, prompt user.
            MsgBox "Password must be numeric.", vbOKOnly + vbInformation, "Invalid Password"
            txtPassword.Text = ""
            txtPassword.SetFocus
            Exit Sub
        End If
    Else
        '* Password not entered, advise user.
        MsgBox "Please enter Password.", vbOKOnly + vbInformation, "Invalid Password"
        txtPassword.SetFocus
        Exit Sub
    End If
    
    strUser = txtUser.Text
    strPass = txtPassword.Text
    
    Set adoRstOps = New ADODB.Recordset
    adoRstOps.CursorLocation = adUseClient
    adoRstOps.CursorType = adOpenStatic
    
    strSQL = "SELECT CODE, PASSWORD FROM TB_OPERATORS"
    
    adoRstOps.Open strSQL, adoCnn, adOpenStatic, adLockOptimistic
    
    '* Check that records were returned i.e. User ID.
    If adoRstOps.RecordCount > 0 Then
        '* Record returned check password.
        If strPass = adoRstOps!PASSWORD Then
            MsgBox "You're in!"
        Else
            MsgBox "Password not valid.", vbOKOnly + vbInformation, "Invalid Password"
        End If
    Else
        MsgBox "User ID not valid.", vbOKOnly + vbInformation, "Invalid User ID"
        txtUser.SetFocus
    End If
    
End Sub
 
Private Sub Form_Load()
 
    '* Database connection properties,
    '* normally be either in an ini file or Registry.
    adoCnn.ConnectionTimeout = 25
    adoCnn.Provider = "sqloledb"
    adoCnn.Properties("Data Source").Value = "Datasource"
    adoCnn.Properties("Initial Catalog").Value = "Database"
    adoCnn.Properties("User ID").Value = "sa"
    adoCnn.Properties("Password").Value = "sapwd"
    
    '* Change mouse pointer whilst connecting.
    frmLogin.MousePointer = vbHourglass
    
    '* Open Database.
    adoCnn.Open
    
    '* Change mouse pointer back to default.
    frmLogin.MousePointer = vbDefault
    
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
 
    '* If connected close connection to Database.
    If adoCnn.State = adStateOpen Then
        adoCnn.Close
    End If
    
    End
    
End Sub
 
Private Sub txtUser_Change()
 
    '* Enable OK command button if entry greater than 0.
    If Len(txtUser.Text) > 0 Then
        cmdOK.Enabled = True
    Else
        '* Disable OK command button if entry is 0.
        cmdOK.Enabled = False
    End If
    
End Sub

The above code works fine.

I want the form to show and then establish the connection to the database, and in the event of the database not being available advise the user.

How can I do this?

I've tried putting the Form_Load() event code into the Form_GotFocus() event but the code doesn't run.

Any ideas?

Regards

[j_jewell]

I changed your form_load code to say:

Private Sub Form_Load()
    On Error GoTo ErrorHandler
    '* Database connection properties,
    '* normally be either in an ini file or Registry.
    adoCnn.ConnectionTimeout = 25
    adoCnn.Provider = "sqloledb"
    adoCnn.Properties("Data Source").Value = "ctgsql02"
    adoCnn.Properties("Initial Catalog").Value = "claimsa"
    adoCnn.Properties("Integrated Security").Value = "SSPI"
    
    '* Change mouse pointer whilst connecting.
    frmLogin.MousePointer = vbHourglass
    
    '* Open Database.
    adoCnn.Open
    
    '* Change mouse pointer back to default.
    frmLogin.MousePointer = vbDefault
    Exit Sub
    
ErrorHandler:
    Select Case Err.Number
        Case -2147467259
            MsgBox "The database " & adoCnn.Properties("Initial Catalog").Value & " is not currently available.", vbOKOnly
        Case Else
            Err.Raise Err.Number, Err.Source, Err.Description
    End Select
End Sub

...and I think it does what you are asking, unless I'm just not understanding your problem correctly.

[Pozzi]

That handles the error, but it doesn't allow the form to be shown whilst trying to establish the onnection to the DB.

This is what I'm trying to do....

1) App starts and loads the form.
2) Form loaded/shown.
3) Connection made to DB.
4) Show connection progress in i.e. Label.

If DB not on-line, show user a message, else allow the user to use the form and login.

Regards

[j_jewell]

The error handling that I added, should allow you to check and see if the db is on-line. From there, you know what to do, right?

[RobDog888]

Originally posted by Pozzi
This is what I'm trying to do....

1) App starts and loads the form.
2) Form loaded/shown.
3) Connection made to DB.
4) Show connection progress in i.e. Label.

If DB not on-line, show user a message, else allow the user to use the form and login.

Regards

Try adding a timer to the form and set the .Enabled property to
false and the .Interval property to 10. Then add the code to the
last line of your code in the form load event.
Also move your form load code to a sub called ConnectToSQL.

    'LAST LINE OF FORM_LOAD() EVENT
    Timer1.Enabled = True

End Sub

Private Sub Timer1_Timer()

    Call ConnectToDB()
    Timer1.Enabled = False

End Sub

This will let the form completely load and show. Then after 10 miliseconds the timer
will invoke the ConnectToDB procedure. Allowing you to show
progress, connection state, etc. on your form.

HTH.

[tvssarma]

Hi Pozzi,
you need to put all your code establishing the connection in sub main in a standard module(preferably).
use show method of your login form if the connection succeeds. If it fails you can stop loading the login form and inform the user that there is an error

A better option is to create a splash screen, call sub main on its on_load event.
if connection succeeds, unload the splash screen, show the login form else unload the splash screen and show message.
Hope that helps

[Pozzi]

Hi,

I've tried both ways, Timer & using a Sub and still not getting it to work without problems.

With the Timer the form is frozen, with the sub the form loads but only as an outline.

Surely this shouldn't be this hard to do, and must have been done in the past.

Any samples or additional help would be much appreciated.

Regards