How to complete my code for buttons First, Previous, Next and Last

[Claude du Québec]

Hi everyone,

I am trying to activate the buttons First, Previous, Next and Last on my Form1 (test) and I can have the first record correct, but not the 3 others (Previous, Next and Last)

I also would like to have the BtnPrevious unvisible if the current record is the first and the BtnNext button unvisible if this is the last.

I also would like to have a position field that will change according on which record I will be, something like "Record 8 of 100"

(There is 100 records in my Categories' table.

I am using an SQLControl for all my forms (See code after the Form1 code)

I know this is not that hard, in VBA it would take me 2 minutes, I can't believe how complicated it is in VB.Net, of course I just begin in this new language for me, hopefully someone will find what I am missing in the code.

THANKS in advance

Claude


Form1 image




FForm1's code


Imports System.Data
Imports System.IO
Imports System.Data.SqlClient
Public Class Form1
Inherits Form

Public Sub New()
InitializeComponent()
End Sub

Private SQL As New SqlControl
Private index As Integer = 0
Private last As Integer = 0
Private Sub PopulateData()
Try
'CLEAR EXISTING RECORD
If SQL.DBDS IsNot Nothing Then
SQL.DBDS.Clear()
End If
SQL.RunQuery("SELECT * FROM Categories")
Dim row As DataRow = SQL.DBDS.Tables(0).Rows(index)
Me.CategoryIDTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("CategoryID")
Me.CategoryTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("Category")
Me.ImageDirectoryTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("ImageDirectory")
Me.ProductFromTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("ProductFrom")
Me.ProductToTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("ProductTo")
Me.ImageFromTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("ImageFrom")
Me.ImageToTB.Text = SQL.DBDS.Tables(0).Rows(0).Item("ImageTo")

If SQL.HasException(True) Then Exit Sub
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

Private Sub BtnFirst_Click(sender As Object, e As EventArgs) Handles BtnFirst.Click
index = 0
PopulateData()
End Sub

Private Sub BtnPrevious_Click(sender As Object, e As EventArgs) Handles BtnPrevious.Click
index -= 1
index = If(index < 0, 0, index)
PopulateData()
End Sub

Private Sub BtnNext_Click(sender As Object, e As EventArgs) Handles BtnNext.Click
index += 1
index = If(index > last, last, index)
PopulateData()
End Sub

Private Sub BtnLast_Click(sender As Object, e As EventArgs) Handles BtnLast.Click
index = last - 1
PopulateData()
End Sub

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Try
MdiParent = FrmAdminMenu
Me.Location = New Point(0, 0)
index = 0
PopulateData()
If SQL.HasException(True) Then Exit Sub
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class



SQLControl's code

Imports System.Data.Sql
Imports System.Data.SqlClient
Public Class SqlControl


Public DBCon As New SqlConnection("Server=CLAUDE-PC\192.168.0.133,1433;database=ACData;User=admin;Pwd=mANONbENOIT1954;")

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 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

End Class

Continue reading...