none
Connecting to access database problem RRS feed

  • Question

  • HI guys I working on login program with access database. I'm having problem to with the user for some reason I doesn't want to connect to database, while admin and tech are fine. Thanks
    Option Explicit On
    Imports System.Data.OleDb
    
    Public Class frmLogIn
    
        Dim conn As New OleDbConnection("Provider = Microsoft.Ace.OLEDB.12.0;Data Source=..\..\Files\LoginDatabase.accdb")
        Dim loginAdapter As OleDbDataAdapter = New OleDbDataAdapter
        Dim dsLogin As DataSet = New DataSet()
    
    
    
    
        Private Sub btnLog_Click(sender As Object, e As EventArgs) Handles btnLog.Click
    
    
            Dim dbname, dbpass As String
            Dim loginQuerry As String = "Select * From Users Where username"
    
            dbname = txtUser.Text
            dbpass = txtPass.Text
    
            Try
                conn.Open()
    
                If txtUser.Text = "" Or txtPass.Text = "" Then
                    MessageBox.Show("Please enter your username and password", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    txtUser.Focus()
    
                ElseIf loginQuerry = "Select * From Users Where username ='" & txtUser.Text & "' And password ='" & txtPass.Text & "'" Then
                    loginAdapter.SelectCommand = New OleDbCommand(loginQuerry, conn)
    
    
                ElseIf (txtUser.Text = "admin" And txtPass.Text = "admin") Then
                    frmAdmin.Show()
                    Me.Hide()
    
                Else
                    txtUser.Text = "tech" And txtPass.Text = "tech"
                    frmTechnician.Show()
                    Me.Close()
                End If
    
            Catch ex As Exception
                MessageBox.Show("Something went wrong with database connection")
                txtUser.Text = ""
                txtPass.Text = ""
                txtUser.Focus()
    
            End Try
        End Sub

    Tuesday, April 24, 2018 3:51 PM

Answers

  • I would use a DataReader to check the database for a valid user ID and password. Something like this:

            Dim accessCommand As New System.Data.OleDb.OleDbCommand("Select * From Users Where user = @userID And pass = @pwd", conn)
            accessCommand.Parameters.AddWithValue("userID", txtUser.Text)
            accessCommand.Parameters.AddWithValue("pwd", txtPass.Text)
            Dim accessReader As System.Data.OleDb.OleDbDataReader
            accessReader = accessCommand.ExecuteReader
            If accessReader.Read() Then
                'user ID and password validated
            Else
                'user ID and password validation failed
            End If

    You can remove the DataAdapter code and put this code in your Try...Catch block.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, April 25, 2018 9:59 PM

All replies

  • Hi,

    What programming language is this? I only know VBA but this part doesn't look correct.

                ElseIf loginQuerry = "Select * From Users Where username ='" & txtUser.Text & "' And password ='" & txtPass.Text & "'" Then

    I think you want to execute the query first and then check if it returned a record in your ElseIf statement.

    Just my 2 cents...

    Tuesday, April 24, 2018 4:11 PM
  • Hi, thanks for the post but I made thread  in the wrong forum it suppose to be vb.net. Can moderator move this or should I delete?
    Tuesday, April 24, 2018 4:23 PM
  • Hi,

    In that case, check out this other thread.

    Hope it helps...

    Tuesday, April 24, 2018 4:37 PM
  • Hello BloodySandwich,

    It seems that your issue is more related to VB.net and according to your requirement, I will move the thread to Visual Basic forum.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 25, 2018 2:42 AM
  • Hi BloodySandwich,

    About working on login program with access database, you can take a look the following example:

    http://www.visual-basic-tutorials.com/form/Login-tutorial-database.htm

    https://itsourcecode.com/2017/03/simple-login-form-using-vb-net-ms-access/

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 25, 2018 2:43 AM
    Moderator
  • Are you getting a error? I noticed in your SQL statement the use of the column name "password". This is a reserved word that is used by Microsoft Access. I would recommend renaming it in your database table.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, April 25, 2018 11:44 AM
  • Hello,

    Check out my MSDN code sample that does a login with three attempts for ms-access, also the database is secured with a password so users can not access data without the database login along with their login credentials.

    Login code is in a class project.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, April 25, 2018 1:06 PM
    Moderator
  • Are you getting a error? I noticed in your SQL statement the use of the column name "password". This is a reserved word that is used by Microsoft Access. I would recommend renaming it in your database table.

    Paul ~~~~ Microsoft MVP (Visual Basic)


    Thank you, I have changed the password and username in access file. The only error I'm getting is when try catch exception kicks in with my set message.
    Wednesday, April 25, 2018 3:20 PM
  • Are you getting a error? I noticed in your SQL statement the use of the column name "password". This is a reserved word that is used by Microsoft Access. I would recommend renaming it in your database table.


    Paul ~~~~ Microsoft MVP (Visual Basic)


    Thank you, I have changed the password and username in access file. The only error I'm getting is when try catch exception kicks in with my set message.

    What is the value of ex.Message when the exception occurs? This should indicate what the issue is but it's not displayed in your MessageBox code.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, April 25, 2018 5:54 PM
  • Hi Paul,

    I have fixed the issue by putting loginQuerry before Try Catch. Now my code look like this.  I wonder if that the proper way of putting if statment inside of Try catch, also if I would like to validate user and password inside of access database there is something like OleDbDataReader and the code with this suppose to be put inside try catch statment? Thanks

    Option Explicit On
    Option Strict On
    
    Imports System.Data.OleDb
    
    Public Class frmLogIn
    
        Dim conn As New OleDbConnection("Provider = Microsoft.Ace.OLEDB.12.0;Data Source=..\..\Files\LoginDatabase.accdb")
        Dim loginAdapter As OleDbDataAdapter = New OleDbDataAdapter
        Dim dsLogin As DataSet = New DataSet()
    
    
    
    
        Private Sub btnLog_Click(sender As Object, e As EventArgs) Handles btnLog.Click
    
    
            Dim dbname, dbpass As String
    
    
            dbname = txtUser.Text
            dbpass = txtPass.Text
    
            Dim loginQuerry As String = "Select * From Users Where user ='" & txtUser.Text & "' And pass ='" & txtPass.Text & "'"
            loginAdapter.SelectCommand = New OleDbCommand(loginQuerry, conn)
    
            Try
                conn.Open()
    
                If txtUser.Text = "" And txtPass.Text = "" Then
                    MessageBox.Show("Please enter your username and password", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    txtUser.Focus()
    
                ElseIf (txtUser.Text = "admin" And txtPass.Text = "admin") Then
                    frmAdmin.Show()
                    Me.Hide()
    
                ElseIf txtUser.Text = "tech" And txtPass.Text = "tech" Then
                    frmTechnician.Show()
                    Me.Hide()
                Else
                    frmUser.Show()
                    Me.Hide()
    
                End If
    
            Catch ex As Exception
                MessageBox.Show("Something went wrong with database connection")
                txtUser.Text = ""
                txtPass.Text = ""
                txtUser.Focus()
    
    
    
            End Try
        End Sub




    Wednesday, April 25, 2018 8:12 PM
  • I would use a DataReader to check the database for a valid user ID and password. Something like this:

            Dim accessCommand As New System.Data.OleDb.OleDbCommand("Select * From Users Where user = @userID And pass = @pwd", conn)
            accessCommand.Parameters.AddWithValue("userID", txtUser.Text)
            accessCommand.Parameters.AddWithValue("pwd", txtPass.Text)
            Dim accessReader As System.Data.OleDb.OleDbDataReader
            accessReader = accessCommand.ExecuteReader
            If accessReader.Read() Then
                'user ID and password validated
            Else
                'user ID and password validation failed
            End If

    You can remove the DataAdapter code and put this code in your Try...Catch block.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, April 25, 2018 9:59 PM