C
Claude du Québec
Guest
Hi,
I have a form on which I put a listbox with all records from my Categories table. On the same form, I have a binding navigator that works perfectly.
Both are working well but are not connecting to each other. If I am going on record number 10 with the binding navigator and I click on the item 5 in the list box, then, all data are change correctly and all the fields are showing item 5 information, however, the binding navigator stays at number 10.
1- Is it possible to link the list box with the binding navigator?
2- And when I use the binding navigator, can the listbox's cursor move and shows the same item then the binding navigator?
There is an image and I also put the code, thanks for your time
UPDATE:
I found how to update my binding navigator when clicking on an item in the listbox, so only question 2 remains.
In case someone is interested here the code I enter to resolve question no 1
Private Sub LBCategories_SelectedIndexChanged(sender As Object, e As EventArgs) Handles LBCategories.SelectedIndexChanged
GetCategoryDetails(LBCategories.Text)
Dim index As Integer
index = Me.CategoryIDTB.Text
If CategoriesBindingSource.Position <> index Then
'it was found, change position to that record
CategoriesBindingSource.Position = index
End If
End Sub
Code:
Imports System.Data
Imports System.Data.SqlClient
Public Class FrmCategories
Private SQL As New SQLControl
Private Sub BtnClose_Click(sender As Object, e As EventArgs) Handles BtnClose.Click
Me.BtnSaveAll.PerformClick()
If Me.CheckBox1.Checked = False Then
FrmMustSaveData.Show()
FrmMustSaveData.Location = New System.Drawing.Point(225, 250)
FrmMustSaveData.TopMost = True
Else
Me.Close()
End If
End Sub
Private Sub BtnNormal_Click(sender As Object, e As EventArgs) Handles BtnNormal.Click
WindowState = FormWindowState.Normal
Me.BtnMaximize.Visible = True
Me.BtnNormal.Visible = False
End Sub
Private Sub BtnMinimize_Click(sender As Object, e As EventArgs) Handles BtnMinimize.Click
WindowState = FormWindowState.Minimized
End Sub
Private Sub BtnMaximize_Click(sender As Object, e As EventArgs) Handles BtnMaximize.Click
WindowState = FormWindowState.Maximized
Me.BtnNormal.Visible = True
Me.BtnMaximize.Visible = False
End Sub
Private Sub FetchCategories()
Try
'REFRESH USER LIST
LBCategories.Items.Clear()
'ADD PARAMS & RUN QUERY
SQL.AddParam("@Category", "%" & TxtFilter.Text & "%")
SQL.ExecQuery("SELECT Category " &
"FROM Categories " &
"WHERE Category LIKE @Category " &
"ORDER BY CategoryID ASC;")
'REPORT & ABORT ON ERRORS
'LOOP ROWS & RETURN USERNAMES TO LIST
For Each r As DataRow In SQL.DBDT.Rows
LBCategories.Items.Add(r("Category"))
Next
If SQL.HasException(True) Then Exit Sub
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Sub FrmCategories_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'TODO: cette ligne de code charge les données dans la table 'ACDataDS.Categories'. Vous pouvez la déplacer ou la supprimer selon les besoins.
Me.CategoriesTableAdapter.Fill(Me.ACDataDS.Categories)
Try
MdiParent = FrmAdminMenu
Me.Location = New Point(0, 0)
LoadFirstRecord()
Me.CountRecords.Text = BindingNavigatorCountItem.ToString
Me.RecordPosition.Text = BindingNavigatorPositionItem.ToString
Me.ImageGB.Visible = True
Me.ProductImageNoGB.Visible = False
If SQL.HasException(True) Then Exit Sub
Catch ex As Exception
MsgBox(ex.Message)
End Try
FetchCategories()
End Sub
Private Sub GetCategoryDetails(Category As String)
'ADD PARAMS & RUN QUERY
Try
SQL.AddParam("@Category", Category)
SQL.ExecQuery("SELECT * " &
"FROM Categories " &
"WHERE Category = @Category;")
If SQL.RecordCount < 1 Then Exit Sub
For Each r As DataRow In SQL.DBDT.Rows
CategoryIDTB.Text = r("CategoryID")
CategoryTB.Text = r("Category")
ImageDirectoryTB.Text = r("ImageDirectory").ToString
ProductFromTB.Text = r("ProductFrom").ToString
ProductToTB.Text = r("ProductTo").ToString
ImageFromTB.Text = r("ImageFrom").ToString
ImageToTB.Text = r("ImageTo").ToString
Next
If Me.CategoryIDTB.Text > 40 Then
Me.ImageGB.Visible = False
Me.ProductImageNoGB.Visible = True
Me.ProductImageNoGB.Location = New System.Drawing.Point(243, 225)
Else
Me.ImageGB.Visible = True
Me.ProductImageNoGB.Visible = False
End If
Me.CheckBox1.Checked = False
If SQL.HasException = True Then Exit Sub
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Public Sub LoadFirstRecord()
Try
'CLEAR EXISTING RECORD
If SQL.DBDS IsNot Nothing Then
SQL.DBDS.Clear()
End If
SQL.RunQuery("SELECT * FROM Categories Where CategoryID = 1")
If SQL.DBDS.Tables(0).Rows(0).Item(0) >= 1 Then
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")
Else
MsgBox("There is no Category 1, please ask the system administrator!", MsgBoxStyle.Critical, "NO RECORD FOUND")
End If
If SQL.HasException(True) Then Exit Sub
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Sub LBCategories_SelectedIndexChanged(sender As Object, e As EventArgs) Handles LBCategories.SelectedIndexChanged
GetCategoryDetails(LBCategories.Text)
End Sub
Private Sub TxtFilter_KeyDown(sender As Object, e As KeyEventArgs) Handles TxtFilter.KeyDown
If e.KeyCode = Keys.Enter Then
FetchCategories()
e.Handled = True
e.SuppressKeyPress = True
End If
End Sub
Private Sub UpdateCategories()
'ADD SQL PARAMS & RUN THE COMMAND
Try
SQL.AddParam("@CategoryID", CategoryIDTB.Text)
SQL.AddParam("@Category", CategoryTB.Text)
SQL.AddParam("@ImageDirectory", ImageDirectoryTB.Text)
SQL.AddParam("@ProductFrom", ProductFromTB.Text)
SQL.AddParam("@ProductTo", ProductToTB.Text)
SQL.AddParam("@ImageFrom", ImageFromTB.Text)
SQL.AddParam("@ImageTo", ImageToTB.Text)
SQL.ExecQuery("UPDATE Categories " &
"SET Category=@Category,ImageDirectory=@ImageDirectory,ProductFrom=@ProductFrom,ProductTo=@ProductTo,ImageFrom=@ImageFrom,ImageTo=@ImageTo WHERE CategoryID=@CategoryID;")
If SQL.HasException(True) Then Exit Sub
'MessageBox.Show("YOUR CHANGES HAS BEEN SAVED.", "Data saved")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Sub BtnSaveAll_Click(sender As Object, e As EventArgs) Handles BtnSaveAll.Click
Me.CheckBox1.Checked = True
UpdateCategories()
End Sub
Private Sub ButtonFirst_Click(sender As Object, e As EventArgs) Handles ButtonFirst.Click
BindingNavigatorMoveFirstItem.PerformClick()
Me.RecordPosition.Text = BindingNavigatorPositionItem.ToString
End Sub
Private Sub ButtonPrevious_Click(sender As Object, e As EventArgs) Handles ButtonPrevious.Click
If Me.CategoryIDTB.Text = 1 Then
MsgBox("You are at the first record of the dataset!", MsgBoxStyle.Information, "FIRST RECORD")
End If
BindingNavigatorMovePreviousItem.PerformClick()
Me.RecordPosition.Text = BindingNavigatorPositionItem.ToString
End Sub
Private Sub ButtonNext_Click(sender As Object, e As EventArgs) Handles ButtonNext.Click
GetRowsCount()
BindingNavigatorMoveNextItem.PerformClick()
Me.RecordPosition.Text = BindingNavigatorPositionItem.ToString
End Sub
Private Sub ButtonLast_Click(sender As Object, e As EventArgs) Handles ButtonLast.Click
BindingNavigatorMoveLastItem.PerformClick()
Me.RecordPosition.Text = BindingNavigatorPositionItem.ToString
End Sub
Private Sub GetRowsCount()
Try
'CLEAR EXISTING RECORD
If SQL.DBDS IsNot Nothing Then
SQL.DBDS.Clear()
End If
SQL.RunQuery("SELECT * FROM Categories")
If Me.CategoryIDTB.Text = SQL.DBDS.Tables(0).Rows.Count Then
MsgBox("You are at the last record of the dataset!", MsgBoxStyle.Information, "LAST RECORD")
End If
If SQL.HasException(True) Then Exit Sub
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class
Continue reading...
I have a form on which I put a listbox with all records from my Categories table. On the same form, I have a binding navigator that works perfectly.
Both are working well but are not connecting to each other. If I am going on record number 10 with the binding navigator and I click on the item 5 in the list box, then, all data are change correctly and all the fields are showing item 5 information, however, the binding navigator stays at number 10.
1- Is it possible to link the list box with the binding navigator?
2- And when I use the binding navigator, can the listbox's cursor move and shows the same item then the binding navigator?
There is an image and I also put the code, thanks for your time
UPDATE:
I found how to update my binding navigator when clicking on an item in the listbox, so only question 2 remains.
In case someone is interested here the code I enter to resolve question no 1
Private Sub LBCategories_SelectedIndexChanged(sender As Object, e As EventArgs) Handles LBCategories.SelectedIndexChanged
GetCategoryDetails(LBCategories.Text)
Dim index As Integer
index = Me.CategoryIDTB.Text
If CategoriesBindingSource.Position <> index Then
'it was found, change position to that record
CategoriesBindingSource.Position = index
End If
End Sub
Code:
Imports System.Data
Imports System.Data.SqlClient
Public Class FrmCategories
Private SQL As New SQLControl
Private Sub BtnClose_Click(sender As Object, e As EventArgs) Handles BtnClose.Click
Me.BtnSaveAll.PerformClick()
If Me.CheckBox1.Checked = False Then
FrmMustSaveData.Show()
FrmMustSaveData.Location = New System.Drawing.Point(225, 250)
FrmMustSaveData.TopMost = True
Else
Me.Close()
End If
End Sub
Private Sub BtnNormal_Click(sender As Object, e As EventArgs) Handles BtnNormal.Click
WindowState = FormWindowState.Normal
Me.BtnMaximize.Visible = True
Me.BtnNormal.Visible = False
End Sub
Private Sub BtnMinimize_Click(sender As Object, e As EventArgs) Handles BtnMinimize.Click
WindowState = FormWindowState.Minimized
End Sub
Private Sub BtnMaximize_Click(sender As Object, e As EventArgs) Handles BtnMaximize.Click
WindowState = FormWindowState.Maximized
Me.BtnNormal.Visible = True
Me.BtnMaximize.Visible = False
End Sub
Private Sub FetchCategories()
Try
'REFRESH USER LIST
LBCategories.Items.Clear()
'ADD PARAMS & RUN QUERY
SQL.AddParam("@Category", "%" & TxtFilter.Text & "%")
SQL.ExecQuery("SELECT Category " &
"FROM Categories " &
"WHERE Category LIKE @Category " &
"ORDER BY CategoryID ASC;")
'REPORT & ABORT ON ERRORS
'LOOP ROWS & RETURN USERNAMES TO LIST
For Each r As DataRow In SQL.DBDT.Rows
LBCategories.Items.Add(r("Category"))
Next
If SQL.HasException(True) Then Exit Sub
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Sub FrmCategories_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'TODO: cette ligne de code charge les données dans la table 'ACDataDS.Categories'. Vous pouvez la déplacer ou la supprimer selon les besoins.
Me.CategoriesTableAdapter.Fill(Me.ACDataDS.Categories)
Try
MdiParent = FrmAdminMenu
Me.Location = New Point(0, 0)
LoadFirstRecord()
Me.CountRecords.Text = BindingNavigatorCountItem.ToString
Me.RecordPosition.Text = BindingNavigatorPositionItem.ToString
Me.ImageGB.Visible = True
Me.ProductImageNoGB.Visible = False
If SQL.HasException(True) Then Exit Sub
Catch ex As Exception
MsgBox(ex.Message)
End Try
FetchCategories()
End Sub
Private Sub GetCategoryDetails(Category As String)
'ADD PARAMS & RUN QUERY
Try
SQL.AddParam("@Category", Category)
SQL.ExecQuery("SELECT * " &
"FROM Categories " &
"WHERE Category = @Category;")
If SQL.RecordCount < 1 Then Exit Sub
For Each r As DataRow In SQL.DBDT.Rows
CategoryIDTB.Text = r("CategoryID")
CategoryTB.Text = r("Category")
ImageDirectoryTB.Text = r("ImageDirectory").ToString
ProductFromTB.Text = r("ProductFrom").ToString
ProductToTB.Text = r("ProductTo").ToString
ImageFromTB.Text = r("ImageFrom").ToString
ImageToTB.Text = r("ImageTo").ToString
Next
If Me.CategoryIDTB.Text > 40 Then
Me.ImageGB.Visible = False
Me.ProductImageNoGB.Visible = True
Me.ProductImageNoGB.Location = New System.Drawing.Point(243, 225)
Else
Me.ImageGB.Visible = True
Me.ProductImageNoGB.Visible = False
End If
Me.CheckBox1.Checked = False
If SQL.HasException = True Then Exit Sub
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Public Sub LoadFirstRecord()
Try
'CLEAR EXISTING RECORD
If SQL.DBDS IsNot Nothing Then
SQL.DBDS.Clear()
End If
SQL.RunQuery("SELECT * FROM Categories Where CategoryID = 1")
If SQL.DBDS.Tables(0).Rows(0).Item(0) >= 1 Then
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")
Else
MsgBox("There is no Category 1, please ask the system administrator!", MsgBoxStyle.Critical, "NO RECORD FOUND")
End If
If SQL.HasException(True) Then Exit Sub
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Sub LBCategories_SelectedIndexChanged(sender As Object, e As EventArgs) Handles LBCategories.SelectedIndexChanged
GetCategoryDetails(LBCategories.Text)
End Sub
Private Sub TxtFilter_KeyDown(sender As Object, e As KeyEventArgs) Handles TxtFilter.KeyDown
If e.KeyCode = Keys.Enter Then
FetchCategories()
e.Handled = True
e.SuppressKeyPress = True
End If
End Sub
Private Sub UpdateCategories()
'ADD SQL PARAMS & RUN THE COMMAND
Try
SQL.AddParam("@CategoryID", CategoryIDTB.Text)
SQL.AddParam("@Category", CategoryTB.Text)
SQL.AddParam("@ImageDirectory", ImageDirectoryTB.Text)
SQL.AddParam("@ProductFrom", ProductFromTB.Text)
SQL.AddParam("@ProductTo", ProductToTB.Text)
SQL.AddParam("@ImageFrom", ImageFromTB.Text)
SQL.AddParam("@ImageTo", ImageToTB.Text)
SQL.ExecQuery("UPDATE Categories " &
"SET Category=@Category,ImageDirectory=@ImageDirectory,ProductFrom=@ProductFrom,ProductTo=@ProductTo,ImageFrom=@ImageFrom,ImageTo=@ImageTo WHERE CategoryID=@CategoryID;")
If SQL.HasException(True) Then Exit Sub
'MessageBox.Show("YOUR CHANGES HAS BEEN SAVED.", "Data saved")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Sub BtnSaveAll_Click(sender As Object, e As EventArgs) Handles BtnSaveAll.Click
Me.CheckBox1.Checked = True
UpdateCategories()
End Sub
Private Sub ButtonFirst_Click(sender As Object, e As EventArgs) Handles ButtonFirst.Click
BindingNavigatorMoveFirstItem.PerformClick()
Me.RecordPosition.Text = BindingNavigatorPositionItem.ToString
End Sub
Private Sub ButtonPrevious_Click(sender As Object, e As EventArgs) Handles ButtonPrevious.Click
If Me.CategoryIDTB.Text = 1 Then
MsgBox("You are at the first record of the dataset!", MsgBoxStyle.Information, "FIRST RECORD")
End If
BindingNavigatorMovePreviousItem.PerformClick()
Me.RecordPosition.Text = BindingNavigatorPositionItem.ToString
End Sub
Private Sub ButtonNext_Click(sender As Object, e As EventArgs) Handles ButtonNext.Click
GetRowsCount()
BindingNavigatorMoveNextItem.PerformClick()
Me.RecordPosition.Text = BindingNavigatorPositionItem.ToString
End Sub
Private Sub ButtonLast_Click(sender As Object, e As EventArgs) Handles ButtonLast.Click
BindingNavigatorMoveLastItem.PerformClick()
Me.RecordPosition.Text = BindingNavigatorPositionItem.ToString
End Sub
Private Sub GetRowsCount()
Try
'CLEAR EXISTING RECORD
If SQL.DBDS IsNot Nothing Then
SQL.DBDS.Clear()
End If
SQL.RunQuery("SELECT * FROM Categories")
If Me.CategoryIDTB.Text = SQL.DBDS.Tables(0).Rows.Count Then
MsgBox("You are at the last record of the dataset!", MsgBoxStyle.Information, "LAST RECORD")
End If
If SQL.HasException(True) Then Exit Sub
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class
Continue reading...