Solved

Displaying data from database through labels

Posted on 2006-11-04
34
200 Views
Last Modified: 2011-10-03
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
0
Comment
Question by:seanrhudy
  • 19
  • 13
  • 2
34 Comments
 
LVL 34

Expert Comment

by:Sancler
ID: 17875044
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
0
 

Author Comment

by:seanrhudy
ID: 17875067
Thanks for the advice, I know how to bind the data to the lablels, the problem is, in this scenario, I need everything to be on the form at once, So I need to list all of this information, not scroll through the records, and the amount of labels needed, will never be known, because they will be different every day.
0
 

Author Comment

by:seanrhudy
ID: 17875209
Do I need to use an IF statement?
0
 
LVL 6

Expert Comment

by:Marv-in
ID: 17875943
you can loop through the datatable and add the labels dynamically:      

       For Each row As DataRow In DataTable
            Dim L As New Label
            L.Parent = HostForm '//need to have a ref to the host form
            L.Text = row("field")
            L.Location = New Point(100, 100) '//neet to calc dynamic location
        Next

but this might be more trouble than its worth
0
 

Author Comment

by:seanrhudy
ID: 17875946
how do I do the location, because each row of data needs to be on one line down.
0
 
LVL 6

Expert Comment

by:Marv-in
ID: 17875971
dim start as integer = 100 '// keep track of where to start
dim offset as integer= 20 '//and how far to move next label
dim row_count as integer=0

       For Each row As DataRow In DataTable
            Dim L As New Label
            L.Parent = HostForm '//need to have a ref to the host form
            L.Text = row("field")
            L.Location = New Point(100, start+(offset+row_count)) '//this is where the label will be drawn on the form
            row_count+= offset
        Next
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17875982
Or, similar

   Dim NewTop As Integer = 0 ' or some other start location from the top

Then, as the last step in the loop

   NewTop += 24 ' or some figure dervied from your label height plus any margin you want

And alter

            L.Location = New Point(100, 100) '//neet to calc dynamic location

to

            L.Location = New Point(100, NewTop) '//neet to calc dynamic location

Having said that, I'm not sure why you want to use labels.  Have you considered a datagrid with some of its properties altered?  On these lines

        Me.DataGrid1.BorderStyle = System.Windows.Forms.BorderStyle.None
        Me.DataGrid1.CaptionVisible = False
        Me.DataGrid1.ColumnHeadersVisible = False
        Me.DataGrid1.GridLineStyle = System.Windows.Forms.DataGridLineStyle.None
        Me.DataGrid1.ReadOnly = True
        Me.DataGrid1.RowHeadersVisible = False

That particular set of settings may not achieve precisely what you want, but you might find it worth experimenting a bit on those lines to avoid having to dynamically add labels in this way.

Roger
0
 
LVL 6

Accepted Solution

by:
Marv-in earned 250 total points
ID: 17876037
I agree with Sancler - if you can use a grid do it - it will save you in the long run
0
 

Author Comment

by:seanrhudy
ID: 17878210
I wanted to pull that data into "Visual Documents" to make a print form as well.  Can I still pass that data using the datagrid?
0
 

Author Comment

by:seanrhudy
ID: 17878263
For example, here is another page that I have.  This one is passing in variables from labels, a set amount of labels.  How would I have to change this code, to make it do what I need for this?
 
        'Print Variables
       
        myBrush = New SolidBrush(Color.FromArgb(255, 18, 0, 0))
        myfont = New Font("MS Sans Serif", 10, FontStyle.Regular)
        e.Graphics.DrawString(Me.cborcvdby.Text, myfont, myBrush, 2.5, 3.28125)
        myBrush = New SolidBrush(Color.FromArgb(255, 18, 0, 0))
        myfont = New Font("MS Sans Serif", 10, FontStyle.Regular)
        e.Graphics.DrawString(Me.txtfirstname.Text, myfont, myBrush, 2.729167, 3.052083)
        myBrush = New SolidBrush(Color.FromArgb(255, 18, 0, 0))
        myfont = New Font("MS Sans Serif", 10, FontStyle.Regular)
        e.Graphics.DrawString(Me.txtlastname.Text, myfont, myBrush, 3.59375, 3.041667)
        myBrush = New SolidBrush(Color.FromArgb(255, 18, 0, 0))
        myfont = New Font("MS Sans Serif", 10, FontStyle.Regular)
        e.Graphics.DrawString(Me.txtreceiptno.Text, myfont, myBrush, 6.65625, 1.34375)
        myBrush = New SolidBrush(Color.FromArgb(255, 18, 0, 0))
        myfont = New Font("MS Sans Serif", 10, FontStyle.Regular)
        e.Graphics.DrawString(Me.txtdate.Text, myfont, myBrush, 6.666667, 1.53125)
        myBrush = New SolidBrush(Color.FromArgb(255, 18, 0, 0))
        myfont = New Font("MS Sans Serif", 10, FontStyle.Regular)
        e.Graphics.DrawString(Me.txtnote.Text, myfont, myBrush, 1.270833, 4.84375)
        myBrush = New SolidBrush(Color.FromArgb(255, 18, 0, 0))
        myfont = New Font("MS Sans Serif", 10, FontStyle.Regular)
        e.Graphics.DrawString(Me.txtpmt1.Text, myfont, myBrush, 5.395833, 3.229167)
        myBrush = New SolidBrush(Color.FromArgb(255, 18, 0, 0))
        myfont = New Font("MS Sans Serif", 10, FontStyle.Regular)
        e.Graphics.DrawString(Me.txtpmt2.Text, myfont, myBrush, 5.40625, 3.4375)
        myBrush = New SolidBrush(Color.FromArgb(255, 18, 0, 0))
        myfont = New Font("MS Sans Serif", 10, FontStyle.Regular)
        e.Graphics.DrawString(Me.txtpmt3.Text, myfont, myBrush, 5.416667, 3.645833)
        myBrush = New SolidBrush(Color.FromArgb(255, 18, 0, 0))
        myfont = New Font("MS Sans Serif", 10, FontStyle.Regular)
        e.Graphics.DrawString(Me.txttotal.Text, myfont, myBrush, 5.416667, 4.052083)
        myBrush = New SolidBrush(Color.FromArgb(255, 18, 0, 0))
        myfont = New Font("MS Sans Serif", 10, FontStyle.Regular)
        e.Graphics.DrawString(Me.cboaccount.Text, myfont, myBrush, 2.447917, 3.75)
        myBrush = New SolidBrush(Color.FromArgb(255, 18, 0, 0))
        myfont = New Font("MS Sans Serif", 10, FontStyle.Regular)
        e.Graphics.DrawString(Me.txtpolicyno.Text, myfont, myBrush, 1.177083, 3.75)



        e.HasMorePages = False
    End Sub
0
 
LVL 6

Expert Comment

by:Marv-in
ID: 17879928
if you are doing reports from an sql query then i would suggest addin a report to your project. that will do alot of the work for you - just bind to your dataset - even has some autoformat features.
0
 

Author Comment

by:seanrhudy
ID: 17881037
2 questions, 1. Is there any way to do it this way? 2. Is the reports item built into visual studio?
0
 

Author Comment

by:seanrhudy
ID: 17881225
Also, since I will be passing data to labels or a datagrid, cant I just pass those into the print form? instead of using sql statements?
0
 
LVL 6

Expert Comment

by:Marv-in
ID: 17883108
If you add a New Item to your project of the type Report you will see what I am talking about.

You can use the report to bind to your datasource and print all the fields and data you want in a nice table view with just a little bit of code.
0
 

Author Comment

by:seanrhudy
ID: 17883308
Ok, i created a new report named eofreport .  Should I use the list item from the reports toolbox? If so, how do I configure it to only show the sql statement from above?
0
 
LVL 6

Expert Comment

by:Marv-in
ID: 17883715
If you add a data source to the datasource tab you can just drag the fields onto the report

honestly I use a 3rd party report control - but the ms one should have all the capability that you need.

I can work out some examples if you need later.
0
 

Author Comment

by:seanrhudy
ID: 17883806
Yes, please, I can drag the fields over, but how will that filter the search to only find rows where the pdate column value = null? and also, how can I dynamically add the items because I will never know how many items will be needed on the report?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 6

Expert Comment

by:Marv-in
ID: 17885626
http://msdn2.microsoft.com/en-us/library/ms251724(VS.80).aspx

check out that link - decent step by step

basically you should be able to connect to your datasource and use the sql query and have a report with paging and all.

looks like the table or matrix contorl will be what you  need to drop the fields from your datasource into
0
 

Author Comment

by:seanrhudy
ID: 17886251
Just thought of something else.  In this report, will I be able to add up all the totals of the total column and display it at the bottom of the report?  From first glance, I dont see a way to do this.
0
 

Author Comment

by:seanrhudy
ID: 17892316
Ok, I found a way to do it using Visual Documents, It does reports as well.....Very nice program....

Back to the form, If I put everything in a datagrid, how can I add up the totals of one of the "total" column and display it in a textbox below the datagrid?
0
 
LVL 6

Expert Comment

by:Marv-in
ID: 17893316
easy way would be to use the row pre paint event of the datagridview and add up the fields you want and store in a global var
0
 

Author Comment

by:seanrhudy
ID: 17893331
could you give me an example?
0
 
LVL 6

Expert Comment

by:Marv-in
ID: 17897679
   Private Sub dg3_RowPrePaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPrePaintEventArgs) Handles dg3.RowPrePaint

        Dim total As Double = 0.0
        total += dg3.Rows(e.RowIndex).Cells("fieldname").Value

        Label1.Text = FormatCurrency(total)

    End Sub
0
 

Author Comment

by:seanrhudy
ID: 17900168
Ok, so dg3 is the name of the datagrid? and obviously I need to place my field name in there, is there anything else I need to replace?
0
 
LVL 6

Expert Comment

by:Marv-in
ID: 17901345
label1 would be the textbox that you want to show the total in - i used a label. but that should do what you want - its also a nice event to change row/cell styles like colors and such
0
 

Author Comment

by:seanrhudy
ID: 17901613
Where would I call this routine?
0
 
LVL 6

Expert Comment

by:Marv-in
ID: 17905611
I had to made two changes below - that way it will track the total correctly.

the datagridview calls the event - thats what the'Handles dg3.RowPrePaint' does


  Private Sub dg3_RowPrePaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPrePaintEventArgs) Handles dg3.RowPrePaint

        Static Total As Double = 0.0 '//change

        If e.RowIndex = 0 Then Total = 0.0 '//change
        total += dg3.Rows(e.RowIndex).Cells("fieldname").Value

        Label1.Text = FormatCurrency(total)

    End Sub
       
0
 

Author Comment

by:seanrhudy
ID: 17915520
will this work in the regular datagrid as well, or do i have to use datagridview?
0
 
LVL 6

Expert Comment

by:Marv-in
ID: 17916232
If you are using 2005 the datagridview is better. I dont have 2003 installed here at work so I will need to check when I get home.

I believe there is a smilar even you can use.
0
 

Author Comment

by:seanrhudy
ID: 17916397
the datagridview is harder to customize... i thought
0
 

Author Comment

by:seanrhudy
ID: 17916480
I have 2005
0
 

Author Comment

by:seanrhudy
ID: 17916582
the prepaint did not work, it is putting my backgroud on the datagrid from my local PC. Here is the code I have.

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
        'TODO: This line of code loads data into the 'ReceiptDataSet4.pdatequery' table. You can move, or remove it, as needed.
        Me.PdatequeryTableAdapter.Fill(Me.ReceiptDataSet4.pdatequery)
        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)
        con.Close()
    End Sub

    Private Sub btnBack_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBack.Click
        Me.Visible = False
        frontpage.Visible = True
    End Sub
    Private Sub dg3_RowPrePaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPrePaintEventArgs) Handles eodgrid.RowPrePaint

        Static Total As Double = 0.0 '//change

        If e.RowIndex = 0 Then Total = 0.0 '//change
        Total += eodgrid.Rows(e.RowIndex).Cells("total").Value

        txteodtotal.Text = FormatCurrency(Total)

    End Sub

End Class
0
 

Author Comment

by:seanrhudy
ID: 17919252
I also changed the Private Sub to eodgrid_RowPrePaint......
0
 
LVL 6

Expert Comment

by:Marv-in
ID: 17920986
Let me give you a bunch of code examples - and we can go from there.

here is some code for formatting - m_grid is the actual datagridview obj
Private Sub InitMSGrid(ByVal BackColor As Drawing.Color, ByVal ForeColor As Drawing.Color)

        With m_Grid
            .MultiSelect = False
            .AllowUserToAddRows = False
            .EditMode = Windows.Forms.DataGridViewEditMode.EditProgrammatically
            .SelectionMode = Windows.Forms.DataGridViewSelectionMode.FullRowSelect
            .MultiSelect = False
            .AllowUserToResizeRows = False
        End With

        With m_Grid

            With .RowTemplate
                .Height = 18
            End With

            With .DefaultCellStyle
                .BackColor = BackColor
                .ForeColor = ForeColor
            End With

        End With
    End Sub

here is some code to hide cols from user
For Each col As DataColumn In m_datatable.Columns
            Select Case col.ColumnName.ToLower
                Case "name", "type"
                    '//dont hide these cols
                Case Else
                    col.ColumnMapping = MappingType.Hidden
            End Select
        Next

code snipit of how i fill a datatable - i dont usually use a dataset if i dont need to
Dim sqlTest As String = "SELECT status FROM tower.ap WHERE ...."
                Dim da As New OracleDataAdapter(sqlTest, OraConn)
                Dim tdt As New DataTable
                da.Fill(tdt)

The other thing you can do for a total is include it in your sql
select sum(field) as GrandTotal <- you can add this in oracle but im not sure about access - you might have to have a second select


let me know if i have confused you yet =]
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now