asked on

Displaying data from database through labels

Hello, I have a form called "endofday". In the page load events, I need it to go into my access database, pull all items that have a NULL value for the "pdate" column, and display them on the screen. Some times it may be 5, sometimes it may be 20. I would like it to be in labels if possilbe. The columns that I want to display are, ReceiptNo, firstname, lastname. I assume there needs to be an IF statement, but do not know where to start. Here is the code that I have so far.

Imports System.Data
Public Class Endofday
Dim con As New OleDb.OleDbConnection
Dim sql As String
Dim ds As New DataSet
Private Cmd As New OleDb.OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Private Sub Endofday_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/backup/receiptprogram/receiptmanagement/receipt.mdb"
con.Open()
Dsendofday.Clear()
Cmd.CommandText = "SELECT ReceiptNo, PolicyNo, pmt1, pmt2, pmt3, type1, type2, type3, total, rcvdby, firstname, lastname, typeaccount, company, checkmaker, checkno, Datercvd, notebox FROM Receiptinfo WHERE pdate = null"
Cmd.CommandType = CommandType.Text
da = New OleDb.OleDbDataAdapter(Cmd)
da.Fill(Dsendofday.Receiptinfo)
End Sub
End Class

Sancler

What's wrong with your code so far is that

1) you don't need explicitly to open your connection when you are using a dataadapter. That handles opening and closing of the relevant connection itself - leaving it as it finds it. BUT ...

2) you do need to tell the dataadapter what the connection is. You can do this by setting a Command's .Connection property. In terms of your code that would be

Cmd.Connection = con

So your code might look like this

Imports System.Data
Public Class Endofday
Dim con As New OleDb.OleDbConnection
Dim sql As String
Dim ds As New DataSet
Private Cmd As New OleDb.OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Private Sub Endofday_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/backup/receiptprogram/receiptmanagement/receipt.mdb"
Dsendofday.Clear()
Cmd.CommandText = "SELECT ReceiptNo, PolicyNo, pmt1, pmt2, pmt3, type1, type2, type3, total, rcvdby, firstname, lastname, typeaccount, company, checkmaker, checkno, Datercvd, notebox FROM Receiptinfo WHERE pdate = null"
Cmd.CommandType = CommandType.Text
Cmd.Connection = con
da = New OleDb.OleDbDataAdapter(Cmd)
da.Fill(Dsendofday.Receiptinfo)
End Sub
End Class

That should result in the datatable Dsendofday.Receiptinfo containing the records you want.

If you then want to display just some fields from one of those records in labels you would need to bind the .Text property of the relevant label to the relevant field in the datatable. The code for that is like this

lblReceiptNo.DataBindings.Add("Text", Dsendofday.Receiptinfo, "ReceiptNo")

The arguments in that are: "Text" is the property of the control that you want to bind; Dsendofday.Receiptinfo is the datasource (in this case, the datatable) that you want to bind to; and "ReceiptNo" is the datamember (in this case, the specific field within that datatable that you want to bind to).

To be able to change which record your labels are displaying you will need a currency manager. You get that by code like this

Dim cm As CurrencyManager

at form level and then

cm = BindingContext(Dsendofday.Receiptinfo)

in your form load sub after you have filled the datatable.

And you will need some buttons to move to the next record or the previous record. Their code will be

cm.Position +=1

to move to the next record or

cm.Position -=1

to move to the previous record.

I suspect that that is not quite all that you will want. It will suffice if all you want to do is look at the records concerned. But, usually, records are displayed so that they can be altered in some way. But if that is the case here you will need to come back with some more details about what you want to do.

Roger