Displaying data from database to form

[totoy13]

Hi, I'm new to the forum and I was wondering if anyone would know how to display data from the tables of a MS Access database on labels on a form.

The code underneath is my code for saving inputted data from textboxes.

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        objDA = New OleDbDataAdapter("SELECT * FROM CDetails", "Provider=Microsoft.Jet.OLEDB.4.0;Password=;" & "User ID=Admin;Data Source =" & Application.StartupPath & "\CustomerDetails.mdb")
        objDS = New Data.DataSet()
        objCB = New OleDbCommandBuilder(objDA)
        objDA.Fill(objDS, "CDetails")

        Dim objRow As Data.DataRow
        With objDS.Tables(0)
            objRow = .NewRow
            objRow("FirstName") = txtFirst.Text = FirstName
            objRow("Surname") = txtSur.Text
            objRow("Address") = txtAddress.Text
            objRow("AreaCode") = txtArea.Text
            objRow("State") = txtState.Text
            objRow("Country") = txtCountry.Text
            objRow("HomePh") = txtHome.Text
            objRow("DOB") = txtDob.Text
            .Rows.Add(objRow)
        End With
        'Updates the database with the row
        objDA.Update(objDS, "CDetails")
        MsgBox("Details Saved")
    End Sub

I would like to show these saved details onto another form. Is there a way to select rows and cells in the database and fetch the data in them to display them onto labels?

Thanks alot!

[jbisono]

Take a look of this thread http://www.daniweb.com/forums/thread203951.html

if still need help let me know.

[kvprajapati]

Welcome totoy13,
To show single value based upon the primary key or something like that. For example,

....
objDA = New OleDbDataAdapter("SELECT * FROM CDetails where  FirstName='totoy13'", "Provider=Microsoft.Jet.OLEDB.4.0;Password=;" & "User ID=Admin;Data Source =" & Application.StartupPath & "\CustomerDetails.mdb")
        objDS = New Data.DataSet()
        objCB = New OleDbCommandBuilder(objDA)
        objDA.Fill(objDS, "CDetails")
        Dim Dt as DataTable = objDS.Tables("CDetails)
        If Dt.Rows.Count<>0 then
             Label1.Text= Dt.Rows(0)("FirstName")
             Label2.Text=Dt.Rows(0)(1) ' SurName or someting... 
             ....
       End IF

If you want to show all records then use DataGridView control.

...
objDA = New OleDbDataAdapter("SELECT * FROM CDetails", "Provider=Microsoft.Jet.OLEDB.4.0;Password=;" & "User ID=Admin;Data Source =" & Application.StartupPath & "\CustomerDetails.mdb")
        objDS = New Data.DataSet()
        objCB = New OleDbCommandBuilder(objDA)
        objDA.Fill(objDS, "CDetails")
        Dim Dt as DataTable = objDS.Tables("CDetails)
        DataGridView1.DataSource=Dt
        ...

[totoy13]

Hi,

Thanks for your responses, however, I can't seem to get it to execute without errors.

Imports System.Data.OleDb
Public Class CustomerReceipt
    Dim objDA As OleDbDataAdapter
    Dim objDS As New Data.DataSet()
    Dim objCB As OleDbCommandBuilder
    Dim Dt As DataTable = objDS.Tables("CDetails")
    
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        objDA = New OleDbDataAdapter("SELECT * FROM CDetails where FirstName='totoy13'", "Provider=Microsoft.Jet.OLEDB.4.0;Password=;" & "User ID=Admin;Data Source =" & Application.StartupPath & "\CustomerDetails.mdb")
        objDS = New Data.DataSet()
        objCB = New OleDbCommandBuilder(objDA)
        objDA.Fill(objDS, "CDetails")

        If Dt.Rows.Count <> 0 Then
            lblFirst.Text = Dt.Rows(0)("FirstName")
            lblSur.Text = Dt.Rows(0)(1)("Surname")
        End If
End Sub

End Class

I get an error with "If Dt.Rows.Count <> 0 Then". It says "Object reference not set to an instance of an object."

Thanks so much for your help.

[kvprajapati]

Imports System.Data.OleDb
Public Class CustomerReceipt
    Dim objDA As OleDbDataAdapter
    Dim objDS As New Data.DataSet()
    Dim objCB As OleDbCommandBuilder
    Dim Dt As DataTable
    
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        objDA = New OleDbDataAdapter("SELECT * FROM CDetails where FirstName='totoy13'", "Provider=Microsoft.Jet.OLEDB.4.0;Password=;" & "User ID=Admin;Data Source =" & Application.StartupPath & "\CustomerDetails.mdb")
        objDS = New Data.DataSet()
        objCB = New OleDbCommandBuilder(objDA)
        objDA.Fill(objDS, "CDetails")
        '---------------------- 
        Dt=objDS.Tables("CDetails")
        '---------------------
        If Dt.Rows.Count <> 0 Then
            lblFirst.Text = Dt.Rows(0)("FirstName")
            lblSur.Text = Dt.Rows(0)(1)("Surname")
        End If
End Sub
End Class

[totoy13]

Thank you so much!

I was wondering how you could select rows, as it seems that it always shows the data on the first row of the table. How do I move down rows?

Thanks again!

[kvprajapati]

Use DataGridView Control.
Dt.Rows is collection of Table's rows. Index 0 for first record, 1 for 2nd and so on.

Dim I as Integer
   For I=0 to Dt.Rows.Count - 1
            ...  Dt.Rows(I)("FirstName")
            ..   Dt.Rows(I)(1) ' SurName or someting... 
   Next

Mark this thread as "Solved" if you find appropriate solution.

[totoy13]

Very sorry for the late reply. However I currently have this:

objDA = New OleDbDataAdapter("SELECT * FROM CDetails where FirstName='Hernando'", "Provider=Microsoft.Jet.OLEDB.4.0;Password=;" & "User ID=Admin;Data Source =" & Application.StartupPath & "\CustomerDetails.mdb")
        objDS = New Data.DataSet()
        objCB = New OleDbCommandBuilder(objDA)
        objDA.Fill(objDS, "CDetails")

        Dim Dt As DataTable = objDS.Tables("CDetails")

        For I = 0 To Dt.Rows.Count - 1

            lblFirst.Text = Dt.Rows(I)("FirstName")

            lblSur.Text = Dt.Rows(I)(1)("SurName")

        Next

The "FirstName" shows the data properly, but "SurName" gives an error:

lblSur.Text = Dt.Rows(I)(1)("SurName") is highlighted:
"Conversion from string "SurName" to type 'Integer' is not valid"

Thanks again