none
Extract data from access database and display in TextBox RRS feed

  • Question

  • Hi all

    I am wanting to display specific data from my access database. My access database has 3 rows

    UserName

    UserPassword

    UserTimer

    I can write information to my database no problems, but what I would really like is to be able to display a specific row in 3 TextBox's I have on my windows form.

    So when someone clicks on a button let's say "Button3" it ill display the username password and timer (which is a number) in the 3 textbox's of whom ever it was that logged in

    Now you will see in my code below I can get it to display the information of the person who logged in. But this information comes from the login form not the database. I can also get it to display the first row of information in the database. But once again I still can not get he information from the database of whom ever logs in.

    Can anyone help out by giving me an example. I have included what I believe is the relevant code.

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
            dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
            dbSource = "Data Source = C:\Users\Nige\Documents\Visual Studio 2012\Projects\MS_Access_SimplePassword\bin\Debug\Database1.mdb"
    
            con.ConnectionString = dbProvider & dbSource
            con.Open()
            sql = "SELECT * FROM tblContacts"
            da = New OleDb.OleDbDataAdapter(sql, con)
            da.Fill(ds, "database1")
            'MsgBox("Database is now open")
    
            con.Close()
            MaxRows = ds.Tables("database1").Rows.Count
            inc = -1
            'MsgBox("Database is now Closed")
            txtFirstName.Text = CStr(ds.Tables("database1").Rows(0).Item(1))
            txtSurname.Text = CStr(ds.Tables("database1").Rows(0).Item(2))
            txtTimer.Text = CStr(ds.Tables("database1").Rows(0).Item(3))
    
            'The code below just checks what user logged in and displays there name in a label.
            'I was thinking maybe I could somehow incorporate this code. So that the logged in user has there name displayed 'in the label (like it is now) and the database get's searched for what ever name appears in the label.
    
            LoginForm.txtUserName.Text = LoginForm.txtUserName.Text
    
            If LoginForm.txtUserName.Text <> "" Then
    
            End If
            lblName.Text = LoginForm.txtUserName.Text
            con.Close()
        End Sub

    Imports System.Data.OleDb
    
    Public Class frmMainform
        Private LoginAttempts As Integer
        Dim ds As New DataSet
        Dim dbProvider As String
        Dim dbSource As String
        Dim inc As Integer
        Dim con As New OleDb.OleDbConnection
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        Dim WelcomeName As String
        Dim MaxRows As Integer
        Dim UserTimer As Integer

    Thanks


    Friday, June 7, 2013 11:19 AM

Answers

  • Hello,

    In Button1 all data is returned and each field excluding the primary key are bound to TextBox controls while Button2 gets the first row only, sets each field to a TextBox.

    VS2010/VS2012, VB.NET

    Public Class Form1
        WithEvents bsData As New BindingSource
        Dim Builder As New OleDb.OleDbConnectionStringBuilder With
            {
                .Provider = "Microsoft.Jet.OLEDB.4.0",
                .DataSource = "C:\Data\NorthWind.mdb"
            }
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        End Sub
        ''' <summary>
        ''' Get all rows
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        ''' <remarks></remarks>
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Using cn As New OleDb.OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDb.OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText =
                        <SQL>
                            SELECT
                                Identifier, 
                                CompanyName, 
                                ContactName, 
                                Country 
                            FROM Customers
                        </SQL>.Value
                    }
                    Dim dt As New DataTable
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    bsData.DataSource = dt
                    BindingNavigator1.BindingSource = bsData
                End Using
            End Using
            txtCompanyName.DataBindings.Add("Text", bsData, "CompanyName")
            txtContactName.DataBindings.Add("Text", bsData, "ContactName")
            txtCountry.DataBindings.Add("Text", bsData, "Country")
        End Sub
        ''' <summary>
        ''' Get first row only
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        ''' <remarks></remarks>
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Using cn As New OleDb.OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDb.OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText =
                        <SQL>
                            SELECT TOP 1 
                                Identifier, 
                                CompanyName, 
                                ContactName, 
                                Country 
                            FROM Customers
                        </SQL>.Value
                    }
                    cn.Open()
                    Dim Reader As OleDb.OleDbDataReader = cmd.ExecuteReader
                    If Reader.HasRows Then
                        Reader.Read()
                        txtIdentifier.Text = Reader.GetInt32(0).ToString
                        txtCompanyName.Text = Reader.GetString(1)
                        txtContactName.Text = Reader.GetString(2)
                        txtCountry.Text = Reader.GetString(3)
                    End If
                End Using
            End Using
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    • Marked as answer by nigelsvision Friday, July 19, 2013 5:09 PM
    Friday, June 7, 2013 2:01 PM
    Moderator

All replies

  • Hello,

    In Button1 all data is returned and each field excluding the primary key are bound to TextBox controls while Button2 gets the first row only, sets each field to a TextBox.

    VS2010/VS2012, VB.NET

    Public Class Form1
        WithEvents bsData As New BindingSource
        Dim Builder As New OleDb.OleDbConnectionStringBuilder With
            {
                .Provider = "Microsoft.Jet.OLEDB.4.0",
                .DataSource = "C:\Data\NorthWind.mdb"
            }
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        End Sub
        ''' <summary>
        ''' Get all rows
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        ''' <remarks></remarks>
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Using cn As New OleDb.OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDb.OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText =
                        <SQL>
                            SELECT
                                Identifier, 
                                CompanyName, 
                                ContactName, 
                                Country 
                            FROM Customers
                        </SQL>.Value
                    }
                    Dim dt As New DataTable
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    bsData.DataSource = dt
                    BindingNavigator1.BindingSource = bsData
                End Using
            End Using
            txtCompanyName.DataBindings.Add("Text", bsData, "CompanyName")
            txtContactName.DataBindings.Add("Text", bsData, "ContactName")
            txtCountry.DataBindings.Add("Text", bsData, "Country")
        End Sub
        ''' <summary>
        ''' Get first row only
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        ''' <remarks></remarks>
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Using cn As New OleDb.OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDb.OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText =
                        <SQL>
                            SELECT TOP 1 
                                Identifier, 
                                CompanyName, 
                                ContactName, 
                                Country 
                            FROM Customers
                        </SQL>.Value
                    }
                    cn.Open()
                    Dim Reader As OleDb.OleDbDataReader = cmd.ExecuteReader
                    If Reader.HasRows Then
                        Reader.Read()
                        txtIdentifier.Text = Reader.GetInt32(0).ToString
                        txtCompanyName.Text = Reader.GetString(1)
                        txtContactName.Text = Reader.GetString(2)
                        txtCountry.Text = Reader.GetString(3)
                    End If
                End Using
            End Using
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    • Marked as answer by nigelsvision Friday, July 19, 2013 5:09 PM
    Friday, June 7, 2013 2:01 PM
    Moderator
  • Thanks you are a superstar
    • Edited by nigelsvision Friday, July 19, 2013 5:10 PM forgot my manners
    Friday, June 7, 2013 6:28 PM