Question MySQL Classes

[dukeofawesome]

Hi Everyone,

I am teaching myself classes using VB.Net and connecting to a MySQL DB. I am really stumped and hope someone can help me out. I have a Database class and table class that I have manipulated and changed from various resources around teh internet but can't make it work. Here is my DB Class:

Public Class clsDatabase
    Private objConn As MySqlConnection
    Private objCmd As MySqlCommand
    Private Trans As MySqlTransaction
    Private strConnString As String

    Public Sub New()
        'strConnString = System.Configuration.ConfigurationSettings.AppSettings("ConnectionString")
        strConnString = System.Configuration.ConfigurationManager.AppSettings("ConnectionString")
    End Sub

    Public Function QueryDataReader(ByVal strSQL As String) As MySqlDataReader
        Dim dtReader As MySqlDataReader
        objConn = New MySqlConnection
        With objConn
            .ConnectionString = strConnString
            .Open()
        End With
        objCmd = New MySqlCommand(strSQL, objConn)
        dtReader = objCmd.ExecuteReader()
        Return dtReader '*** Return DataReader ***'
    End Function

    Public Function QueryDataSet(ByVal strSQL As String) As DataSet
        Dim ds As New DataSet
        Dim dtAdapter As New MySqlDataAdapter
        objConn = New MySqlConnection
        With objConn
            .ConnectionString = strConnString
            .Open()
        End With
        objCmd = New MySqlCommand
        With objCmd
            .Connection = objConn
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        dtAdapter.SelectCommand = objCmd
        dtAdapter.Fill(ds)
        Return ds   '*** Return DataSet ***'
    End Function

    Public Function QueryDataTable(ByVal strSQL As String) As DataTable
        Dim dtAdapter As MySqlDataAdapter
        Dim dt As New DataTable
        objConn = New MySqlConnection
        With objConn
            .ConnectionString = strConnString
            .Open()
        End With
        dtAdapter = New MySqlDataAdapter(strSQL, objConn)
        dtAdapter.Fill(dt)
        Return dt '*** Return DataTable ***'
    End Function

    Public Function QueryExecuteNonQuery(ByVal strSQL As String) As Boolean
        objConn = New MySqlConnection
        With objConn
            .ConnectionString = strConnString
            .Open()
        End With
        Try
            objCmd = New MySqlCommand()
            With objCmd
                .Connection = objConn
                .CommandType = CommandType.Text
                .CommandText = strSQL
            End With
            objCmd.ExecuteNonQuery()
            Return True '*** Return True ***'
        Catch ex As Exception
            Return False '*** Return False ***'
        End Try
    End Function

    Public Function QueryExecuteScalar(ByVal strSQL As String) As Object
        Dim obj As Object
        objConn = New MySqlConnection
        With objConn
            .ConnectionString = strConnString
            .Open()
        End With
        Try
            objCmd = New MySqlCommand()
            With objCmd
                .Connection = objConn
                .CommandType = CommandType.Text
                .CommandText = strSQL
            End With
            obj = objCmd.ExecuteScalar()  '*** Return Scalar ***'
            Return obj
        Catch ex As Exception
            Return Nothing '*** Return Nothing ***'
        End Try
    End Function

    Public Function TransStart()
        objConn = New MySqlConnection
        With objConn
            .ConnectionString = strConnString
            .Open()
        End With
        Trans = objConn.BeginTransaction(IsolationLevel.ReadCommitted)
    End Function

    Public Function TransExecute(ByVal strSQL As String) As Boolean
        objCmd = New MySqlCommand()
        With objCmd
            .Connection = objConn
            .Transaction = Trans
            .CommandType = CommandType.Text
            .CommandText = strSQL
        End With
        objCmd.ExecuteNonQuery()
    End Function

    Public Function TransRollBack()
        Trans.Rollback()
    End Function

    Public Function TransCommit()
        Trans.Commit()
    End Function

    Public Sub Close()
        objConn.Close()
        objConn = Nothing
    End Sub

End Class

And here is the start of my table class:

Public Class clsUsers

#Region "Constants"
    Public Shared _Table As String = "tblusers"
#End Region

#Region " Instance Variables "
    Private _UserID As Integer = 0
    Private _Username As String = ""
    Private _Email As String = ""
    Private _Comment As String = ""
    Private _Password As String = ""
    Private _PasswordQuestion As String = ""
    Private _PasswordAnswer As String = ""
    Private _LastActivityDate As String = ""
    Private _LastLoginDate As String = ""
    Private _LastPasswordChangedDate As String = ""
    Private _CreationDate As String = ""
    Private _IsApproved As Integer = 0
    Private _IsLockedOut As Integer = 0
    Private _LastLockedOutDate As String = ""
    Private _FailedPasswordAttemptCount As Integer = 0
    Private _FailedPasswordAttemptDateTimeStart As String = ""
    Private _FailedPasswordAnswerAttemptCount As Integer = 0
    Private _FailedPasswordAnswerAttemptDateTimeStart As String = ""
#End Region

#Region " Constructor "
    Public Sub New()
        'Do nothing as all private variables has been initiated
    End Sub

    Public Sub New(ByVal DataRow As Data.DataRow) 'DataRow)
        _UserID = CInt(DataRow.Item("UserID"))
        _Username = CStr(DataRow.Item("Username"))
        _Email = CStr(DataRow.Item("Email"))
        _Comment = CStr(DataRow.Item("Comment"))
        _Password = CStr(DataRow.Item("Password"))
        _PasswordQuestion = CStr(DataRow.Item("PasswordQuestion"))
        _PasswordAnswer = CStr(DataRow.Item("PasswordAnswer"))
        _LastActivityDate = CStr(DataRow.Item("LastActivityDate"))
        _LastLoginDate = CStr(DataRow.Item("LastLoginDate"))
        _LastPasswordChangedDate = CStr(DataRow.Item("LastPasswordChangedDate"))
        _CreationDate = CStr(DataRow.Item("CreationDate"))
        _IsApproved = CInt(DataRow.Item("IsApproved"))
        _IsLockedOut = CInt(DataRow.Item("IsLockedOut"))
        _LastLockedOutDate = CStr(DataRow.Item("LastLockedOutDate"))
        _FailedPasswordAttemptCount = CInt(DataRow.Item("FailedPasswordAttemptCount"))
        _FailedPasswordAttemptDateTimeStart = CStr(DataRow.Item("FailedPasswordAttemptDateTimeStart"))
        _FailedPasswordAnswerAttemptCount = CInt(DataRow.Item("FailedPasswordAnswerAttemptCount"))
        _FailedPasswordAnswerAttemptDateTimeStart = CStr(DataRow.Item("FailedPasswordAnswerAttemptDateTimeStart"))
    End Sub
#End Region

#Region " Properties "
    Public Property UserID() As Integer
        Get
            Return _UserID
        End Get
        Set(ByVal value As Integer)
            _UserID = value
        End Set
    End Property
    Public Property Username() As String
        Get
            Return _Username
        End Get
        Set(ByVal value As String)
            _Username = value
        End Set
    End Property
#End Region
End Class

What I am having trouble with is when I try to use the reader in the following code:

        Dim obj As New clsUsers
        Dim strSQL As String = ("select * from tblusers")
        Dim dr As MySqlDataReader

        While dr.Read()
            If dr("UserID") <> DBNull.Value Then

            End If
        End While

I get an error saying Operator '<>' is not defined for types 'Object' and 'System.DBNull'.

Can anyone tell me how to get this to work?

Many thanks

Duke

 

[jmcilhinney]

Most classes can't be compared using = and <>. They are generally just for structures or the relatively small number of classes that have an = operator defined. To test referential equality, which is what you're doing here, you need to use Is and IsNot.