dukeofawesome
Member
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:
And here is the start of my table class:
What I am having trouble with is when I try to use the reader in the following code:
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
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:
VB.NET:
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:
VB.NET:
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:
VB.NET:
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