Hi everyone,
I have a classe named SQLControl and a sub named RunQuery, how can I modify that sub to show this message if the result returns no record:
"There is no record for this query, please try again" I am receiving the message "There is no row in position 0." and it should be easy to change that message no?
I show you all the class.
Imports System.Data.SqlClient
Public Class SQLControl
Public UsernameFriend As String
Public ConnectionString As String = ""
Public ConnectionObj As System.Data.SqlClient.SqlConnection = Nothing
Public TransactionObj As System.Data.SqlClient.SqlTransaction = Nothing
Public DBCon As New SqlConnection("Data Source=ACSERVER\ACRSERVER;Initial Catalog=ACData;Integrated Security=SSPI;")
Public DBCmd As SqlCommand
'DB DATA
Public DBDA As SqlDataAdapter
Public DBDT As DataTable
Public DBDS As DataSet
Public DBDR As SqlDataReader
'QUERY PARAMETERS
Public Params As New List(Of SqlParameter)
'QUERY STATISTICS
Public RecordCount As Integer
Public Exception As String
Public Sub New()
End Sub
'ALLOW CONNECTION STRING OVERRIDE
Public Sub New(ConnectionString As String)
DBCon = New SqlConnection(ConnectionString)
End Sub
Public Function CloseDatabase(ByRef ErrorMSG As String) As Boolean
Try
Me.Rollback()
TransactionObj.Dispose()
ConnectionObj.Close()
ConnectionObj.Dispose()
Return True
Catch ex As Exception
ErrorMSG = ex.Message
Return False
End Try
End Function
Public Sub Rollback()
TransactionObj.Rollback()
TransactionObj.Dispose()
TransactionObj = ConnectionObj.BeginTransaction
End Sub
Public Function HasConnection() As Boolean
Try
DBCon.Open()
DBCon.Close()
Return True
Catch ex As Exception
MsgBox(ex.Message)
End Try
Return False
End Function
Public Sub RunQuery(Query As String)
Try
DBCon.Open()
'CREATE COMMAND
DBCmd = New SqlCommand(Query, DBCon)
'FILL DATASET
DBDA = New SqlDataAdapter(DBCmd)
DBDS = New DataSet
DBDA.Fill(DBDS)
DBCon.Close()
Catch ex As Exception
MsgBox(ex.Message)
'MAKE SURE CONNECTION IS CLOSE
If DBCon.State = ConnectionState.Open Then
DBCon.Close()
End If
End Try
End Sub
Public Sub GetSQLQueries(Query As String)
Try
DBCon.Open()
DBCmd = New SqlCommand(Query, DBCon)
' LOAD SQL RECORDS IN A DATAGRID
DBDA = New SqlDataAdapter(DBCmd)
DBDS = New DataSet
DBDA.Fill(DBDS)
DBCon.Close()
Catch ex As Exception
MsgBox(ex.Message)
'MAKE SURE CONNECTION IS CLOSE
If DBCon.State = ConnectionState.Open Then
DBCon.Close()
End If
End Try
End Sub
'EXECUTE QUERY SUB
Public Sub ExecQuery(Query As String, Optional ReturnIdentity As Boolean = False)
' RESET QUERY STATS
RecordCount = 0
Exception = ""
Try
DBCon.Open()
'CREATE DB COMMAND
DBCmd = New SqlCommand(Query, DBCon)
'LOAD PARAMS INTO DB COMMAND
Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))
' CLEAR PARAMS LIST
Params.Clear()
' EXECUTE COMMAND & FILL DATASET
DBDT = New DataTable
DBDA = New SqlDataAdapter(DBCmd)
DBDS = New DataSet
RecordCount = DBDA.Fill(DBDT)
If ReturnIdentity = True Then
Dim ReturnQuery As String = "SELECT @@IDENTITY As LastID;"
'@@IDENTITY
'SCOPE_IDENTITY() SESSION & SCOPE - To analyse
'IDENT_CURRENT(tablename) to get the last ID in your database, any scope, any session
DBCmd = New SqlCommand(ReturnQuery, DBCon)
DBDT = New DataTable
DBDA = New SqlDataAdapter(DBCmd)
RecordCount = DBDA.Fill(DBDT)
End If
Catch ex As Exception
'CAPTURE ERROR
Exception = "ExecQuery Error: " & vbNewLine & ex.Message
Finally
' CLOSE CONNECTION
If DBCon.State = ConnectionState.Open Then DBCon.Close()
End Try
End Sub
'ADD PARAMS
Public Sub AddParam(Name As String, Value As Object)
Dim NewParam As New SqlParameter(Name, Value)
Params.Add(NewParam)
End Sub
'ERROR CHECKING
Public Function HasException(Optional Report As Boolean = False) As Boolean
If String.IsNullOrEmpty(Exception) Then Return False
If Report = True Then MsgBox(Exception, MsgBoxStyle.Critical, "Exception:")
Return True
End Function
Public Function IsFormOpen(ByVal frm As Form) As Boolean
If Application.OpenForms.OfType(Of Form).Contains(frm) Then
Return True
Else
Return False
End If
End Function
Public Function FillDataTable(SQL As String, ParamArray PRM() As Object) As DataTable
Dim CMD As New SqlClient.SqlCommand With {
.Connection = ConnectionObj,
.Transaction = TransactionObj,
.CommandText = SQL}
Dim I As Integer
For I = 0 To PRM.Count - 1
CMD.Parameters.AddWithValue("@" & I.ToString, PRM(I))
Next
Dim DBDA As New SqlClient.SqlDataAdapter
Dim DBDT As New DataTable
DBDA.SelectCommand = DBCmd
DBDA.Fill(DBDT)
CMD.Dispose()
DBDA.Dispose()
Return DBDT
End Function
Public Sub Commit()
TransactionObj.Commit()
TransactionObj.Dispose()
TransactionObj = ConnectionObj.BeginTransaction
End Sub
Public Sub ExecuteSQL(SQL As String, ParamArray PRM() As Object)
Dim CMD As New SqlClient.SqlCommand With {
.Connection = ConnectionObj,
.Transaction = TransactionObj,
.CommandText = SQL}
Dim I As Integer
For I = 0 To PRM.Count - 1
CMD.Parameters.AddWithValue("@" & I.ToString, PRM(I))
Next
CMD.ExecuteNonQuery()
End Sub
End Class
Thank you
Claude from Quebec, Canada
