connecting to security table in access

[toon10]

I’m trying to create a simple login screen for my application in VB.NET. I’ve recently come over from VB 6! I have a login form which accepts a username and password. I want to lookup an Access table called Security to check that the username and password is valid. If it is then the main form should load.

I have coded what I believe is correct for connecting to the database but I get the following message.

Request for the permission of type ‘system.data.oledb.oledbpermission, system.data, version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ failed

My code doesn’t actually return any records so I’m presuming that my connection isn’t right.

Has anyone got any pointers?

Here’s my code:

Private Sub cmdOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdOK.Click
        Dim ConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\VB.NET \SAP Cost Reporter\Data\pcost.mdb;Persist Security Info=True;Jet OLEDB:Database Password=MyPassword"
        Dim DBCon As New OleDb.OleDbConnection(ConString)

        g_login = Me.txtUserName.Text

        Dim strPassword As String = Me.txtPassword.Text

        If g_login = "" Or strPassword = "" Then
            MsgBox("You are missing information. Please make sure that both the username and password fields are filled out.", MsgBoxStyle.Critical, "Missing Details")
            Me.txtUserName.Focus()
            Return
        End If

        Dim strSQL As String = "SELECT * FROM Security WHERE [username] = '" & g_login & "'"

        Dim cm As New OleDb.OleDbCommand(strSQL, DBCon)
        Dim dr As OleDb.OleDbDataReader
        Dim valid As Boolean = False
        Dim HasRows As Boolean = False
        Try
            DBCon.Open()
            dr = cm.ExecuteReader

            If dr.HasRows Then
                While dr.Read
                    MsgBox(dr.Item("Password"), MsgBoxStyle.Critical, "Error")
                    If strPassword = dr.Item("Password") Then
                        valid = True
                    End If
                End While
                HasRows = True
            End If
            dr.Close()

        Catch exO As OleDb.OleDbException
            MsgBox(exO.Message, MsgBoxStyle.Critical, "Error")

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")

        Finally
            If DBCon.State = ConnectionState.Open Then
                DBCon.Close()
            End If
            cm = Nothing
            dr = Nothing
            DBCon.Dispose()
            GC.Collect()
        End Try

        iCount = iCount + 1
        If valid = True Then
            Me.Hide()
            frmMain.Show()

        ElseIf iCount = 3 Then
            Me.Close()

        ElseIf HasRows = False Then
            MsgBox("Invalid user name, try again!", MsgBoxStyle.Critical, "Invalid Details")
            Me.txtUserName.Focus()
            Me.txtUserName.Text = ""
            Me.txtPassword.Text = ""

        Else
            MsgBox("Invalid password, try again!", MsgBoxStyle.Critical, "Invalid Details")
            Me.txtPassword.Focus()
            Me.txtPassword.Text = ""
        End If

    End Sub

 

[RonRepp]

I think the problem lies in this statement:

Dim strSQL As String = "SELECT * FROM Security WHERE [username] = '" & g_login & "'"

I'm not an Access wizard, but all string variables are surrounded by double quotes.

Dim Q as String = Chr(34) 'Double Quote

Dim strSQL As String = "SELECT * FROM Security WHERE [username] = " & Q &  g_login & Q

I hope this helps.


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[toon10]

Hi

Thanks for the reply. I've tried your suggestion but I get the same result. The select statement works from VB6 the way I have it so I presume that .NET will be the same?

 

[spizotfl]

your sql statement:

Dim strSQL As String = "SELECT * FROM Security WHERE [username] = '" & g_login & "'"

should be fine. single quotes works fine with access.
just hazarding a guess but i see that in the connection string you provide a password but no user name that i see, maybe that is the issue.

"Maturity is a bitter disappointment for which no remedy exists, unless laughter can be said to remedy anything."
-Vonnegut