VB.net: Daily report insert in a chart

Question

I need to create a daily report where my inputs in the database will show in a chart.

Database name: restoDB

table name: tlreports

fields:

ReportID- int

Amount - decimal

Date - datetime

I need to display the current date and time also the amount of sales every hour.

This is my code but it shows all the inputs from all of the dates stored on my database:

Public Sub LoadChart()
        Dim con As New MY_CONNECTION
        Dim Reader As MySqlDataReader

        Dim commanddate = New MySqlCommand("select * from tlreports ", con.getConnection())

        Dim datetime = Val(txtboxDateTime.Text)

        commanddate.Parameters.Add("@datetime", MySqlDbType.VarChar).Value = datetime

        con.openConnection()
        Reader = commanddate.ExecuteReader
        While Reader.Read
            chrtDaily.Series("Daily Income").Points.AddXY(Reader.GetDateTime("Date"), Reader.GetDecimal("Amount"))
        End While

        con.closeConnection()


    End Sub

Welcome to the site! It looks to me that while you're adding the parameter, it's not referenced in the query `New MySqlCommand("select * from tlreports ", con.getConnection())` you need the "where" part. [check this out](https://stackoverflow.com/questions/11139791/how-to-use-parameters-in-an-sql-command-in-vb) — Jimmy Smith, Dec 04 '19 at 16:49
If `datetime` is going to a `VarChar` column, why are you converting it to a number with the `Val` method? Also `Val` is outdated. Use a `,TryParse` method. — Mary, Dec 04 '19 at 19:03
@Mary I don't know how to use it Val() is what our prof teaches to us, but thankyou! :))) — eca, Dec 04 '19 at 23:23
@JimmySmith I tried it first ("select * from tlreports where 'Date'=@datetime") but it is not showing in the chart :((( — eca, Dec 04 '19 at 23:26

score 2 · Accepted Answer · answered Dec 05 '19 at 02:25

Keep your database objects local to the method where they are used so, you can control when they are closed and disposed. Using...End Using blocks take care of this for you even if there is an error.

Only pull down the data you need. In this case you only need the Date and the Amount. You don't have any parameters in your select query so do not add parameters to the command. Your teacher is wrong about Val. This is an old VB6 method. .net has much better methods available. Val returns a Double. You say the Date field is a DateTime field so a Double wouldn't work very well. If someone entered 12/04/2019 in the txtboxDateTime Text Box Val would return 12. Is that what you are expecting?

More on this non-existant parameter. If you did need a parameter I am glad you used the .Add method but you say at the top of you question that the Date field is type datetime but in your .Add method you give it the datatype .VarChar. ???

The problem with using a DataReader is that the connection must stay open while you are building your Series for the chart. Just fill a DataTable and then close and dispose the connection and command with the End Using. Then you can mess with the DataTable to your hearts content.

I am not at all familiar with the Chart control but I don't quite see how a Date can provide an X coordinate.

Public Sub LoadChart()
    Dim dt As New DataTable
    Using con As New MySqlConnection("Your connection string"),
         commanddate As New MySqlCommand("select Date, Amount from tlreports ", con)
        con.Open()
        dt.Load(commanddate.ExecuteReader)
    End Using
    For Each row As DataRow In dt.Rows
        Chart1.Series("Daily Income").Points.AddXY(row("Date"), row("Amount"))
    Next
End Sub

Thankyou for the informations :) can I ask another question? how about displaying the weekly income in the chart using the above table and database? — eca, Dec 05 '19 at 05:41
Make that a new question. Show what you have tried and where it didn't work. — Mary, Dec 05 '19 at 09:12

VB.net: Daily report insert in a chart

1 Answers1