none
Click on the Name on Listbox to fill Textboxes with Username and Password of particular user from SQL database RRS feed

  • Question

  • I want when click on the Name Listed on a Listbox from the database to fill Textboxes with Username and Password of particular user from SQL database.

    I am able to see Names stored on the database on the listbox but when i click on the name its unable to fill the Textboxes.

    Here is the code that fills the Listbox with data from database (NOTE: I am able to view a list of names on the Listbox)

     Public Sub ListBox()
            Try
                If con.State = ConnectionState.Open Then
                    con.Close()

                End If
                con.Open()
                Dim cmd As SqlCommand
                cmd = con.CreateCommand()
                cmd.CommandText = "SELECT * FROM Admin"
                Dim Reader As SqlDataReader
                Reader = cmd.ExecuteReader
                While Reader.Read
                    lbAdmin.Items.Add(Reader.Item("Full Name"))
                End While
            Catch ex As Exception
            End Try
        End Sub

    And here is the code I want to fill the Textboxes after clicking on a name in a Listbox (NOTE: This is not working). I really dont know where I am going wrong, please help.

    Private Sub lbAdmin_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles lbAdmin.SelectedIndexChanged
            Try
                If con.State = ConnectionState.Open Then
                    con.Close()
                End If
                con.Open()

                Dim cmd As SqlCommand
                cmd = con.CreateCommand()
                cmd.CommandText = "SELECT * FROM Admin"
                Dim Reader As SqlDataReader
                Reader = cmd.ExecuteReader
                While Reader.Read
                    txtfullname.Text = Reader.GetString("Full Name")
                    txtusername.Text = Reader.GetString("Username")
                    txtpassword.Text = Reader.GetString("Password")
                End While
            Catch ex As Exception
            End Try
        End Sub

    Sunday, April 14, 2019 6:30 PM

Answers

  • Hi,

    SqlDataReader.GetString(Int32) Method gets the value of the specified column as a string.

    SqlDataReader.GetString(Int32) Method

    fix your code:

    Private Sub lbAdmin_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles lbAdmin.SelectedIndexChanged Try If con.State = ConnectionState.Open Then con.Close() End If con.Open() Dim cmd As SqlCommand cmd = con.CreateCommand() cmd.CommandText = "SELECT * FROM Admin where [Full Name]=@Name"

    cmd.Parameters.AddWithValue("@name", lbAdmin.SelectedItem.ToString) Dim Reader As SqlDataReader Reader = cmd.ExecuteReader While Reader.Read txtfullname.Text = Reader("Full Name") txtusername.Text = Reader("Username") txtpassword.Text = Reader("Password") End While Catch ex As Exception End Try End Sub

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Speed Ack Monday, April 15, 2019 6:28 AM
    Monday, April 15, 2019 3:24 AM
  • Hello,

    I would take a totally different approach, read all data at once, show it and pass along the primary key if there is every a need to reference it back in the database table.

    Example

    Class to represent the users

    Public Class User
        Public Property Id() As Integer
        Public Property UserName() As String
        Public Property UserPassword() As String
        Public Property FullName() As String
        Public Overrides Function ToString() As String
            Return FullName
        End Function
    End Class
    

    In the class below the connection is made via a special class as per the link in my signature

    Imports System.Data.SqlClient
    Imports BaseConnectionLibrary.ConnectionClasses
    Public Class DataOperations
        Inherits SqlServerConnection
        Public Sub New()
            DefaultCatalog = "DemoLogin"
        End Sub
        Public Function ReadUsers() As List(Of User)
            Dim userList = New List(Of User)
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT id,UserName,UserPassword,FullName FROM dbo.Users"
                    cn.Open()
                    Dim reader = cmd.ExecuteReader()
                    While reader.Read()
                        userList.Add(New User() With
                            {
                                .Id = reader.GetInt32(0),
                                .UserName = reader.GetString(1),
                                .UserPassword = reader.GetString(2),
                                .FullName = reader.GetString(3)
                            })
                    End While
    
                End Using
            End Using
            Return userList
        End Function
    End Class

    Form code

    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) _
            Handles MyBase.Load
    
            Dim ops As New DataOperations
            Dim userData As List(Of User) = ops.ReadUsers
            ListBox1.DataSource = userData
    
            userNameTextBox.DataBindings.
                Add("Text", userData, "UserName")
    
            userPasswordTextBox.DataBindings.
                Add("Text", userData, "UserPassword")
    
        End Sub
    End Class
    

    Results


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Speed Ack Tuesday, April 16, 2019 8:41 AM
    Monday, April 15, 2019 10:40 AM
    Moderator
  • Hi Karen,

    Pretty simplified version of handling things ah. Even better for reusing the class on other forms.

    But how do I integrate Form1 above with Update and Delete buttons that can update details on Textboxes in the database as well as delete a selected user name from the Listbox and database.

    I hope I am not asking too much.

    Thank you.

    The high level basics, use a BindingSource and BindingNavigator, override the buttons in the BindingNavigator and use your code to perform those operations. 

    Since this is really a new question and is not a short answer (but when it gets down to coding is not that complex but does require a tad bit of code) I'm just giving you the very basics. And note that Entity Framework does a good deal of this out of the box.

    Lastly, a BindingSource has some pretty cool methods for moving, filtering and knowing things. You could use regular buttons for common data operations instead of a BindingNavigator if they don't suit you.

    Public Class Form1
        Private bsData As New BindingSource
        Private Sub Form1_Load(sender As Object, e As EventArgs) _
            Handles MyBase.Load
    
            Dim ops As New DataOperations
            bsData.DataSource = ops.ReadUsers
            ListBox1.DataSource = bsData
    
            userNameTextBox.DataBindings.
                Add("Text", bsData, "UserName")
    
            userPasswordTextBox.DataBindings.
                Add("Text", bsData, "UserPassword")
    
            BindingNavigator1.BindingSource = bsData
        End Sub
    
        Private Sub BindingNavigatorDeleteItem_Click(sender As Object, e As EventArgs) _
            Handles BindingNavigatorDeleteItem.Click
    
            If bsData.Current IsNot Nothing Then
                Dim person = CType(bsData.Current, User)
                MessageBox.Show($"Delete {person.FullName}")
            End If
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Speed Ack Saturday, April 20, 2019 6:50 PM
    Tuesday, April 16, 2019 10:34 AM
    Moderator

All replies

  • Hi Speed Ack,

    Welcome to the MSDN forum.

    It seems this issue is about the VB.NET development and our forum is to discuss the VS IDE, I will help you move this thread to the appropriate forum for better support, thank you for your understanding.

    Best regards,

    Sara


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, April 15, 2019 1:59 AM
  • Hi,

    SqlDataReader.GetString(Int32) Method gets the value of the specified column as a string.

    SqlDataReader.GetString(Int32) Method

    fix your code:

    Private Sub lbAdmin_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles lbAdmin.SelectedIndexChanged Try If con.State = ConnectionState.Open Then con.Close() End If con.Open() Dim cmd As SqlCommand cmd = con.CreateCommand() cmd.CommandText = "SELECT * FROM Admin where [Full Name]=@Name"

    cmd.Parameters.AddWithValue("@name", lbAdmin.SelectedItem.ToString) Dim Reader As SqlDataReader Reader = cmd.ExecuteReader While Reader.Read txtfullname.Text = Reader("Full Name") txtusername.Text = Reader("Username") txtpassword.Text = Reader("Password") End While Catch ex As Exception End Try End Sub

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Speed Ack Monday, April 15, 2019 6:28 AM
    Monday, April 15, 2019 3:24 AM
  • Hi Alex,

    Thank you for your reply and I am glad to say you have nailed my problem.

    Thank you once again.

    Kind regards,

    Speed Ack.

    Monday, April 15, 2019 6:27 AM
  • Hi Sara,

    Thank you so much. What is the correct/right fora for posting VB.NET discussions?

    Kind Regards,

    Speed.

    Monday, April 15, 2019 6:33 AM
  • Hello,

    I would take a totally different approach, read all data at once, show it and pass along the primary key if there is every a need to reference it back in the database table.

    Example

    Class to represent the users

    Public Class User
        Public Property Id() As Integer
        Public Property UserName() As String
        Public Property UserPassword() As String
        Public Property FullName() As String
        Public Overrides Function ToString() As String
            Return FullName
        End Function
    End Class
    

    In the class below the connection is made via a special class as per the link in my signature

    Imports System.Data.SqlClient
    Imports BaseConnectionLibrary.ConnectionClasses
    Public Class DataOperations
        Inherits SqlServerConnection
        Public Sub New()
            DefaultCatalog = "DemoLogin"
        End Sub
        Public Function ReadUsers() As List(Of User)
            Dim userList = New List(Of User)
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT id,UserName,UserPassword,FullName FROM dbo.Users"
                    cn.Open()
                    Dim reader = cmd.ExecuteReader()
                    While reader.Read()
                        userList.Add(New User() With
                            {
                                .Id = reader.GetInt32(0),
                                .UserName = reader.GetString(1),
                                .UserPassword = reader.GetString(2),
                                .FullName = reader.GetString(3)
                            })
                    End While
    
                End Using
            End Using
            Return userList
        End Function
    End Class

    Form code

    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) _
            Handles MyBase.Load
    
            Dim ops As New DataOperations
            Dim userData As List(Of User) = ops.ReadUsers
            ListBox1.DataSource = userData
    
            userNameTextBox.DataBindings.
                Add("Text", userData, "UserName")
    
            userPasswordTextBox.DataBindings.
                Add("Text", userData, "UserPassword")
    
        End Sub
    End Class
    

    Results


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Speed Ack Tuesday, April 16, 2019 8:41 AM
    Monday, April 15, 2019 10:40 AM
    Moderator
  • Hi Karen,

    Pretty simplified version of handling things ah. Even better for reusing the class on other forms.

    But how do I integrate Form1 above with Update and Delete buttons that can update details on Textboxes in the database as well as delete a selected user name from the Listbox and database.

    I hope I am not asking too much.

    Thank you.

    Tuesday, April 16, 2019 8:41 AM
  • Hi Karen,

    Pretty simplified version of handling things ah. Even better for reusing the class on other forms.

    But how do I integrate Form1 above with Update and Delete buttons that can update details on Textboxes in the database as well as delete a selected user name from the Listbox and database.

    I hope I am not asking too much.

    Thank you.

    The high level basics, use a BindingSource and BindingNavigator, override the buttons in the BindingNavigator and use your code to perform those operations. 

    Since this is really a new question and is not a short answer (but when it gets down to coding is not that complex but does require a tad bit of code) I'm just giving you the very basics. And note that Entity Framework does a good deal of this out of the box.

    Lastly, a BindingSource has some pretty cool methods for moving, filtering and knowing things. You could use regular buttons for common data operations instead of a BindingNavigator if they don't suit you.

    Public Class Form1
        Private bsData As New BindingSource
        Private Sub Form1_Load(sender As Object, e As EventArgs) _
            Handles MyBase.Load
    
            Dim ops As New DataOperations
            bsData.DataSource = ops.ReadUsers
            ListBox1.DataSource = bsData
    
            userNameTextBox.DataBindings.
                Add("Text", bsData, "UserName")
    
            userPasswordTextBox.DataBindings.
                Add("Text", bsData, "UserPassword")
    
            BindingNavigator1.BindingSource = bsData
        End Sub
    
        Private Sub BindingNavigatorDeleteItem_Click(sender As Object, e As EventArgs) _
            Handles BindingNavigatorDeleteItem.Click
    
            If bsData.Current IsNot Nothing Then
                Dim person = CType(bsData.Current, User)
                MessageBox.Show($"Delete {person.FullName}")
            End If
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Speed Ack Saturday, April 20, 2019 6:50 PM
    Tuesday, April 16, 2019 10:34 AM
    Moderator
  • Thank you Karen for a job well done.
    Saturday, April 20, 2019 6:51 PM