Global Module. ADODB Connection and SQL Server

Question

Please help me. I've been stuck here for weeks. I don't know how to solve it.

Here's my connection code:

Imports System.Text.RegularExpressions

Module globalmodule
Public conn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public rss As New ADODB.Recordset
Public trs As New ADODB.Recordset
Public sql As String

Public Function opendb()
    If conn.State = 1 Then conn.Close()
    conn.Open("Provider=SQLOLEDB.1;Data Source=ACER;Initial Catalog=dbEmployee;Integrated Security=True;")
    Return 0
End Function

Function EmailAddressCheck(ByVal emailAddress As String) As Boolean

    Dim pattern As String = "^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$"
    Dim emailAddressMatch As Match = Regex.Match(emailAddress, pattern)

    If emailAddressMatch.Success Then
        EmailAddressCheck = True
    Else
        EmailAddressCheck = False
    End If

    If EmailAddressCheck = False Then
        MsgBox("Entervalid E-mail ID")
    End If

End Function


Public empid As String

End Module

Form 1 which is related to the global module form.

Here's the code:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    opendb()

End Sub

Private Sub btnlogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnlogin.Click

    If cmbutype.Text = "Employee" Then
        sql = "select * from employee where empcode='" & txtuname.Text & "' and password='" & txtupass.Text & "'"

        If rs.State = 1 Then 
           rs.Close()

        rs.Open(sql, conn)

        If rs.EOF = False Then
            MDIMain.MasterToolStripMenuItem.Visible = False
            MDIMain.EmployeeToolStripMenuItem.Visible = False
            MDIMain.SearchToolStripMenuItem.Visible = False
            MDIMain.LeaveToolStripMenuItem.Visible = False
            MDIMain.EarnToolStripMenuItem.Visible = False
            MDIMain.DeductionToolStripMenuItem.Visible = False
            MDIMain.events.Visible = False

            empid = txtuname.Text


            '   MsgBox("login sucess")
            MDIMain.Show()

            Me.Hide()
        End If
    Else
        sql = "select * from login where utypt='" & cmbutype.Text & "' and uname='" & txtuname.Text & "'"

        If rs.State = 1 Then 
           rs.Close()

        rs.Open(sql, conn)

        If rs.EOF = False Then
            sql = "select * from login where utypt='" & cmbutype.Text & "' and uname='" & txtuname.Text & "' and upass='" & txtupass.Text & "'"

            If rs.State = 1 Then 
                rs.Close()

            rs.Open(sql, conn)

            If rs.EOF = False Then
                '  MsgBox("login sucess")
                MDIMain.Show()
                Me.Hide()
            Else
                MsgBox("Incorrect password ")
            End If
        Else
            MsgBox("login failed")

        End If

    End If
End Sub

I am getting errors about adodb.connection is ambiguous in the namespace 'ADODB' and adodb.recordsets is ambiguous in the namespace 'ADODB'

Answer 1

I'll start with this function:

Public Function opendb()
    If conn.State = 1 Then conn.Close()
    conn.Open("Provider=SQLOLEDB.1;Data Source=ACER;Initial Catalog=dbEmployee;Integrated Security=True;")
    Return 0
End Function

VB.Net is not VBScript/VB6. All functions in VB.Net should have a return type. Additionally, best practice for Sql Server is NOT to re-use the same connection object over and over. That breaks the ability for the driver to do effective connection pooling. So you want that the function to look more like this:

'Using ADO.Net objects here because I'm more familiar, and the old ADO objects are really only for backwards compatibility with old code anyway
Public Function opendb() As SqlConnection
                                    'ADO.Net connection string may be slightly different
    Dim result As New SqlConnection("Provider=SQLOLEDB.1;Data Source=ACER;Initial Catalog=dbEmployee;Integrated Security=True;")
    result.Open()
    Return result
End Function

Now let's look at the login code. I'll leave aside for now the huge glaring security issue of storing passwords in plain text (DON'T DO THAT!), and instead focus on the sql injection issues and basic connectivity.

Private Sub btnlogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnlogin.Click
    Dim sql As String = ""
    If cmbutype.Text = "Employee" Then
        sql = "select * from employee where empcode= @Username AND password= @password"
    Else
        sql = "select * from login where utypt= @utype and uname= @username"
    End If

    Using cn As SqlConnection = opendb(), _
          cmd As New SqlCommand(sql, cn)

        'Guessing at column types/lengths for all of these parameters
        cmd.Parameters.Add("@username", SqlDbType.NVarChar, 50).Value = txtuname.Text
        cmd.Parameters.Add("@password", SqlDbType.NVarChar, 64).Value =  txtupass.Text       
        cmd.Parameters.Add("@utype", SqlDbType.VarChar, 15).Value = cmbutype.Text

        cn.Open()
        Dim rdr = cmd.ExecuteReader()

        If Not rdr.Read() Then
            MsgBox("Login Failed")
            Exit Sub
        End If

        If cmbutype.Text <> "Employee" AndAlso rdr("upass").ToString() <> txtupass.Text Then
           MsgBox("Password Incorrect")
           Exit Sub
        End If
    End Using

    '   MsgBox("login sucess")

    If cmbutype.Text = "Employee" Then
        MDIMain.MasterToolStripMenuItem.Visible = False
        MDIMain.EmployeeToolStripMenuItem.Visible = False
        MDIMain.SearchToolStripMenuItem.Visible = False
        MDIMain.LeaveToolStripMenuItem.Visible = False
        MDIMain.EarnToolStripMenuItem.Visible = False
        MDIMain.DeductionToolStripMenuItem.Visible = False
        MDIMain.events.Visible = False

        empid = txtuname.Text
    End If

    MDIMain.Show()
    Me.Hide()       
End Sub

Note I was able to greatly simplify the code (less nesting, and combined some of the logic) because ADO.Net allows you to supply more query parameters than the sql command text actually uses. The older ADODB cannot do this, because it only uses positional parameters.