Results 1 to 34 of 34

Thread: how to calculate with figures from the database

  1. #1

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    40

    how to calculate with figures from the database

    Hi,

    Since I am brand new to the world of VB and willing to learn I would like to know how to retrieve data from 1 column from my database use this to calculate the difference and write it back to my table
    In annex you will find my current DB which is representing the fuel consumption of my car.
    Currently I use excel to do the calculations but I would like to reproduce this through VB.NET

    In the column kmstand you will find the last kmstand when I had to fill my car up
    I know the sql command to retrieve this info
    Code:
    SELECT TOP 1 Verbruik.kmstand
    FROM Verbruik
    ORDER BY Verbruik.id DESC;
    In my form I enter in my textbox the new kmstand then I need to calculate the "dagteller" dagteller = "new" kmstand - "old" kmstand(result of the above query)
    the only thing is I don't know how to do this

    Code:
    Dim sqlQuery as string = "SELECT TOP 1 Verbruik.kmstand
    FROM Verbruik
    ORDER BY Verbruik.id DESC"
    Dim sqlCommand as new Oledb.OledbCommand
    Dim sqlAdapter As New Oledb.OledbDataAdapter
    Dim Table as new DataTable
    Dim NewDagteller as integer
    
    with SqlCommand
          .CommandText = SqlQuery
          .Connection = conn
    end with
    
    with SqlAdapter
          .SelectCommand = SqlCommand
          .Fill(Table)
    ' I'm thinking about a construction like this but not certain that's the right syntax
    NewDagteller.text = Kmstand.text - sqlQuery.

    can someone point me in the right direction

    in attachment you will find the datafile

    Paul
    Attached Files Attached Files

  2. #2
    Code Monkey wild_bill's Avatar
    Join Date
    Mar 2005
    Location
    Montana
    Posts
    2,993

    Re: how to calculate with figures from the database

    1st thing, rather than using a data adapter to fill at table, call your command's execute scalar method. This will give you a single object which is what you need.
    That is the very essence of human beings and our very unique capability to perform complex reasoning and actually use our perception to further our understanding of things. We like to solve problems. -Kleinma

    Does your code in post #46 look like my code in #45? No, it doesn't. Therefore, wrong is how it looks. - jmcilhinney

  3. #3

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    40

    Re: how to calculate with figures from the database

    Wild Bill,
    can you give me an example on how to do this,

    thanks

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: how to calculate with figures from the database

    Quote Originally Posted by paulus4605 View Post
    Wild Bill,
    can you give me an example on how to do this,

    thanks
    Follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data.

  5. #5

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    40

    Re: how to calculate with figures from the database

    Quote Originally Posted by jmcilhinney View Post
    Follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data.
    Would this be the retrieving Access autoNumber topic?

    please keep in mind that I'm a starter (just busy for about 2 weeks )and for what I see in the code is a wee bit of chinese to me

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: how to calculate with figures from the database

    Quote Originally Posted by paulus4605 View Post
    Would this be the retrieving Access autoNumber topic?
    Um, no, it would be the Retrieving & Saving Data topic.

  7. #7

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    40

    Re: how to calculate with figures from the database

    sorry my bad

  8. #8

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    40

    Re: how to calculate with figures from the database

    please correct me if I'm wrong, In my situation I should use the first example in post 1 and enter the following sql SELECT TOP 1 kmstand FROM Verbruik ORDER BY Id DESC

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: how to calculate with figures from the database

    Quote Originally Posted by paulus4605 View Post
    please correct me if I'm wrong, In my situation I should use the first example in post 1 and enter the following sql SELECT TOP 1 kmstand FROM Verbruik ORDER BY Id DESC
    That'll do it.

  10. #10

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    40

    Re: how to calculate with figures from the database

    I managed to get the retrieval part working with your example
    here's the code
    Code:
    Imports System.Data.SqlClient
    
    Public Class Form1
        Dim totalQuantity As Double
    
    
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
    
    
        End Sub
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Using connection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=D:\my documents\Visual Studio 2010\Projects\berekening\berekening\Auto.mdf;Integrated Security=True;User Instance=True")
                Using command As New SqlCommand("SELECT TOP 1 kmstand FROM Verbruik ORDER BY Id DESC", _
                                                connection)
                    connection.Open()
    
                    Dim totalQuantity As Integer = CDbl(command.ExecuteScalar())
                    Dim i As Integer
                    Dim j As Integer
    
                    'Use totalQuantity here.
                    i = Txt1.Text
                    j = i - totalQuantity
                    MsgBox(j)
    
                    
                End Using
            End Using
       End Sub
    End Class
    the next part would be to insert the new data into my database and here I'm stuck
    I was thinking about your example
    Code:
    Using connection As New SqlConnection("connection string here")
        Using command As New SqlCommand("INSERT INTO Verbruik (kmstand, dagteller) VALUES (@kmstand, @dagteller)", _
                                        connection)
            command.Parameters.AddWithValue("@kmstand", i)
            command.Parameters.AddWithValue("@dagteller", j)
           
     
            connection.Open()
     
            command.ExecuteNonQuery()
        End Using
    End Using
    but I don't know how to incorporate this correctly in my code
    please advise
    when I put this just underneath the messagebox I get an error on the command execute nonquery concerning the ExecuteScalar
    Last edited by paulus4605; Mar 30th, 2013 at 03:28 AM.

  11. #11
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: how to calculate with figures from the database

    You've already got an open connection so you just have to create a second command and then change the SQL and parameters to reflect your table and column names.

  12. #12

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    40

    Re: how to calculate with figures from the database

    that's what I thought to and I adapted my code towards this
    Code:
      Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Using connection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=D:\my documents\Visual Studio 2010\Projects\berekening\berekening\Auto.mdf;Integrated Security=True;User Instance=True")
                Using command As New SqlCommand("SELECT TOP 1 kmstand FROM Verbruik ORDER BY Id DESC", _
                                                connection)
                    connection.Open()
    
                    Dim totalQuantity As Integer = CDbl(command.ExecuteScalar())
                    Dim i As Integer
                    Dim j As Integer
    
                    'Use totalQuantity here.
                    i = Txt1.Text
                    j = i - totalQuantity
                    MsgBox(j)
    
    
                    Using command1 As New SqlCommand("INSERT INTO Verbruik (kmstand,dagteller) VALUES (@kmstand, @dagteller)", _
                                       connection)
                        command.Parameters.AddWithValue("@kmstand", i)
                        command.Parameters.AddWithValue("@dagteller", j)
    
    
                        ' connection.Open()
    
                        command1.ExecuteNonQuery()
                    End Using
                End Using
            End Using
    End Sub
    End Class
    but then I get this error
    command1.ExecuteNonQuery() Must declare the scalar variable "@kmstand". so I don't know what I'm doing wrong here

  13. #13
    Frenzied Member IanRyder's Avatar
    Join Date
    Jan 2013
    Location
    Healing, UK
    Posts
    1,232

    Re: how to calculate with figures from the database

    Hi,

    Do yourself a big favour and ALWAYS give your variables DESCRIPTIVE NAMES. As it is, you use Command and Command1 as your SqlCommand variable names and because of this you cannot see the obvious.

    Hope that helps.

    Cheers,

    Ian

  14. #14
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: how to calculate with figures from the database

    Perhaps if you were to give everything a descriptive name, which you should ALWAYS do, your mistake might be more obvious. 'command' and 'command1' are abominable names under those circumstances.

  15. #15

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    40

    Re: how to calculate with figures from the database

    Understood, now I get the error that on id which is my primary key I thought that when you add a new item the id is automaticly incremented by 1 this is the error message
    Cannot insert the value NULL into column 'id', table 'D:\MY DOCUMENTS\VISUAL STUDIO 2010\PROJECTS\BEREKENING\BEREKENING\AUTO.MDF.dbo.Verbruik'; column does not allow nulls. INSERT fails.
    The statement has been terminated.

  16. #16
    Frenzied Member IanRyder's Avatar
    Join Date
    Jan 2013
    Location
    Healing, UK
    Posts
    1,232

    Re: how to calculate with figures from the database

    Hi,

    You need to check your DataTable. It sounds like you have not set the Identify Specification on the ID field. To do this, set Is Identity to Yes and then set the Identity Increment and Identity Seed columns to 1.

    Hope that helps.

    Cheers,

    Ian

  17. #17
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: how to calculate with figures from the database

    IanRyder is quite correct that you need to make your PK column an identity. It's that that makes it set and increment the value automatically. You shouldn't need to worry about the seed and increment, which should both be 1 by default. There are two ways to make the column an identity:

    1. Select the column in the table designer and in the column properties set the Identity Specification to Yes.
    2. Select the table in the table designer and in the table properties set the Identity Column to the appropriate column.

  18. #18

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    40

    Re: how to calculate with figures from the database

    sorry but how do I achieve this I tried to look in properties and such but can't find it sorry

    not able to adapt anything under column properties
    Last edited by paulus4605; Mar 30th, 2013 at 04:47 AM.

  19. #19
    Frenzied Member IanRyder's Avatar
    Join Date
    Jan 2013
    Location
    Healing, UK
    Posts
    1,232

    Re: how to calculate with figures from the database

    Hi,

    Using Server / Database Explorer in VS, open your Database, Expand Your Tables, right click your Table and select Open Table Definition. Then proceed as jmcilhinney has instructed.

    This is also basically the same if you are using SSMS instead.

    Hope that helps.

    Cheers,

    Ian

  20. #20
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: how to calculate with figures from the database

    Quote Originally Posted by IanRyder View Post
    Using Server / Database Explorer in VS, open your Database
    Note that double-clicking on your MDF file in the Solution Explorer will accomplish that, or you can do it directly.

  21. #21

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    40

    Re: how to calculate with figures from the database

    that part is working, is there a way in vb.net to calculate the difference in % between 2 figures?

    there is a formula in Excel that I use which is this one
    =IF(COUNT(Auto[@[verbruik]:[verbruikBoordcomputer]])=2;[@verbruik]/[@verbruikBoordcomputer]-1;"")

    the outcome is for example
    real consumption 5,16 consumption boardcomputer 5,74 difference between these 2 is 10,10%

    secondly is there a way to put real consumption and consumption boardcomputer in a line chart

  22. #22
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: how to calculate with figures from the database

    Quote Originally Posted by paulus4605 View Post
    that part is working, is there a way in vb.net to calculate the difference in % between 2 figures?
    That's basic maths. Basic maths is done in VB the same way it's done with pen and paper. 'a + b' looks the same in VB as it does on paper. How would you calculate this with a pen and paper? That's how you would do it in VB.
    Quote Originally Posted by paulus4605 View Post
    secondly is there a way to put real consumption and consumption boardcomputer in a line chart
    There is a chart control in the VS Toolbox. Add an instance to your form and configure it appropriately. There are samples available but the page seems to be offline right now.

    http://code.msdn.microsoft.com/mscha...ReleaseId=1591

    There's also community content available.

    http://social.msdn.microsoft.com/For...Chart/threads/

  23. #23

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    40

    Re: how to calculate with figures from the database

    ok but how do I get vb to show this in % since the formula is = +RConsumption / Carconsumption - 1 but this gives me -0,1010 and it should be 10,10%

  24. #24
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: how to calculate with figures from the database

    The Math.Abs method will give you the absolute value of a number, so that will take care of the negative sign, i.e. it will give you a positive result no matter which way around the subtraction is performed. You can then call ToString on your number and pass the "p" as the format string, which is for percentage formatting. That will produce a String for display that contains the percentage sign as well as the number multiplied by a factor of 100.

  25. #25

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    40

    Re: how to calculate with figures from the database

    how do you use the winsamples in vb.net?

    especially when I want to use the figures from my database?
    In my example the difference between real consumption and the consumption according to the boardcomputer
    Last edited by paulus4605; Apr 1st, 2013 at 01:19 AM.

  26. #26

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    40

    Re: how to calculate with figures from the database

    I have a problem with this code I think it's because my variables are in the using block and that I'm not able to use them in the second one but I don't know how to keep the values so that I can store them in the database correctly
    Code:
     If CbxZW.Text = "zomer" Then
                        Using avgZomer As New SqlCommand("Select round(avg(RVerbruik),2) from Verbruik where ZomerWinter = 'Zomer'", connection)
                            Dim AvgVerbruikZomer As Double = CDbl(avgZomer.ExecuteScalar())
                            'MsgBox(AvgVerbruikZomer)
                        End Using
                            Else
                            'berekenen van gemiddeld verbruik op winterbanden
                            Using avgWinter As New SqlCommand("Select round(avg(RVerbruik),2) from Verbruik where ZomerWinter = 'Winter'", connection)
    
    
                            Dim AvgVerbruikWinter As Double = CDbl(avgWinter.ExecuteScalar())
                            'MsgBox(AvgVerbruikWinter)
                        End Using
                    End If
                    'berekenen prijs per tank
                    prijsLtr = TxtPrijsLtr.Text
                    prijstank = (Math.Round((prijsLtr * LtrGetankt), 2))
                    MsgBox(prijstank)
                    ZomerWinter = CbxZW.Text
    
                    Using SqlKmStand As New SqlCommand("INSERT INTO Verbruik (kmstand, dagteller, LtrGetankt, LtrBoordComputer, RVerbruik, VbkBoordcomputer, PrijsLtr, PrijsTank, GemZomer, GemWinter, ZomerWinter, datum) VALUES (@kmstand, @dagteller, @LtrGetankt, @LtrBoordComputer, @RVerbruik, @VbkBoordcomputer, @PrijsLtr, @PrijsTank, @GemZomer, @GemWinter, @ZomerWinter, @datum)", _
                                       connection)
                        SqlKmStand.Parameters.AddWithValue("@kmstand", NewKmstand)
                        SqlKmStand.Parameters.AddWithValue("@dagteller", NewDagteller)
                        SqlKmStand.Parameters.AddWithValue("@LtrGetankt", LtrGetankt)
                        SqlKmStand.Parameters.AddWithValue("@LtrBoordComputer", ltrBoordcomputer)
                        SqlKmStand.Parameters.AddWithValue("@RVerbruik", Rverbruik)
                        SqlKmStand.Parameters.AddWithValue("@VbkBoordcomputer", vbkBoordcomputer)
                        SqlKmStand.Parameters.AddWithValue("@PrijsLtr", prijsLtr)
                        SqlKmStand.Parameters.AddWithValue("@PrijsTank", prijstank)
                        SqlKmStand.Parameters.AddWithValue("@GemZomer", AvgVerbruikZomer)
                        SqlKmStand.Parameters.AddWithValue("@GemWinter", AvgVerbruikWinter)
                        SqlKmStand.Parameters.AddWithValue("@ZomerWinter", ZomerWinter)
                        SqlKmStand.Parameters.AddWithValue("@datum", datum)
    
    
    
                        SqlKmStand.ExecuteNonQuery()
                    End Using
                End Using
            End Using
    the problem is that avgVerbruikZomer and AvgVerbruikWinter are empty when I want to store them into the database however they where correctly presented in the previous block of Using just a couple of lines higher
    please advise

  27. #27
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: how to calculate with figures from the database

    A variable cannot be accessed outside the block it's declared in. If you declare a variable inside a Using block then it cannot be accessed outside that Using block. If you want to use a variable outside a Using block then you must declare it outside that Using block. In this case, declare the variables before the first Using block, set their values in the first using block and then use those values in the second Using block.

  28. #28

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    40

    Re: how to calculate with figures from the database

    so I if I understand you correctly I have to declare Dim AvgVerbruikZomer As Double and Dim AvgVerbruikWinter As Double at the top of my project and alter the current code from Dim AvgVerbruikZomer As Double = CDbl(avgZomer.ExecuteScalar()) into AvgVerbruikZomer = CDbl(avgZomer.ExecuteScalar())


    this did the trick thanks a million
    Last edited by paulus4605; Apr 1st, 2013 at 03:49 AM.

  29. #29

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    40

    Re: how to calculate with figures from the database

    jmcilhinney can you use winsamples as well to draw charts with data from a database? and if so how to achieve this (this part is still hanging like fog arround my head and I can't seem to find the light here

  30. #30
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: how to calculate with figures from the database

    Charts will more often than not be used to represent data stored in a database. It really doesn't matter where the data comes from though. The chart doesn't care. The chart just displays a visual representation of some list. That list can be a DataTable populated from a database or some other list, the contents of which may or may not have come from a database.

  31. #31

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    40

    Re: how to calculate with figures from the database

    fair enough you told me to get the chart option in the toolbox so far so good but what's next since I can't seem to get it into my lonely braincell how to get the data from my database transformed into a line chart.
    I'm according to my wife thinking vb out loud in my sleep so you can't blame me for not trying

  32. #32

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    40

    Re: how to calculate with figures from the database

    Hi I have got my project pretty much working the way I want however I have 2 issues left
    1 I finished this project on an other computer and it's working there, however when I run it from this computer I get this error message "Attempted to read or write protected memory. This is often an indication that other memory is corrupt." when I try to call my form where the chart is stored on how do I solve this error.
    2 issue is that I would like to do a refresh of the data so that the graphic is updated to the latest information and that also my totals are updated with the most recent information
    this is the code for the main form
    Code:
    Imports System.Data.SqlClient
    Imports System.Data
    Imports System.Windows.Forms.DataVisualization.Charting
    
    Public Class Form1
        Dim totalQuantity As Double
        Dim AvgVerbruikZomer As Double
        Dim AvgVerbruikWinter As Double
        Dim NewKmstand As Integer
        Dim NewDagteller As Integer
        Dim LtrGetankt As Double
        Dim Rverbruik As Double
        Dim ltrBoordcomputer As Double
        Dim vbkBoordcomputer As Double
        Dim datum As String
        Dim DifVerbruik As Double
        Dim prijstank As Double
        Dim prijsLtr As Double
        Dim ZomerWinter As String
        Dim GemZomer As Double
        Dim GemWinter As Double
        Dim TotKm As Integer
        Dim TotGetankt As Double
        Dim TotRverbruik As Double
        Dim TotVerbruikBc As Double
        Dim Verschil As Double
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
           
    
    
        End Sub
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Using connection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=D:\my documents\Visual Studio 2010\Projects\berekening\berekening\Auto.mdf;Integrated Security=True;User Instance=True")
                Using command As New SqlCommand("SELECT TOP 1 kmstand FROM Verbruik ORDER BY Id DESC", _
                                                connection)
                    connection.Open()
    
                    Dim OldKmstand As Integer = CDbl(command.ExecuteScalar())
    
    
                    NewKmstand = Txt1.Text
                    'dagteller stand berekenen 
                    NewDagteller = NewKmstand - OldKmstand
                    datum = DateTimePicker1.Text
                    LtrGetankt = Txt2.Text
                    ltrBoordcomputer = Txt3.Text
                    'berekenen reel vebruik
                    Rverbruik = (Math.Round((LtrGetankt / NewDagteller * 100), 2))
                    'MsgBox(Rverbruik)
                    'berekenen verbruik boordcomputer
                    vbkBoordcomputer = (Math.Round((ltrBoordcomputer / NewDagteller * 100), 2))
                    'MsgBox(vbkBoordcomputer)
                    'verschil tussen reel verbruik en dagteller berekenen 
                    DifVerbruik = Rverbruik / vbkBoordcomputer - 1
                    'verschil verbruik omzetten in %
                    Dim finaldif As String = FormatPercent(DifVerbruik)
                    'MsgBox(finaldif)
                    'berekenen van gemiddeld verbruik op zomerbanden
                    If CbxZW.Text = "Zomer" Then
                        Using avgZomer As New SqlCommand("Select round(avg(RVerbruik),2) from Verbruik where ZomerWinter = 'Zomer'", connection)
                            AvgVerbruikZomer = CDbl(avgZomer.ExecuteScalar())
                            'MsgBox(AvgVerbruikZomer)
                        End Using
                    Else
                        'berekenen van gemiddeld verbruik op winterbanden
                        Using avgWinter As New SqlCommand("Select round(avg(RVerbruik),2) from Verbruik where ZomerWinter = 'Winter'", connection)
    
    
                            AvgVerbruikWinter = CDbl(avgWinter.ExecuteScalar())
                            'MsgBox(AvgVerbruikWinter)
                        End Using
                    End If
                    'berekenen prijs per tank
                    prijsLtr = TxtPrijsLtr.Text
                    prijstank = (Math.Round((prijsLtr * LtrGetankt), 2))
                    'MsgBox(prijstank)
                    ZomerWinter = CbxZW.Text
                    'gemiddeld verbruik zomer weergeven
                    Using Zomer As New SqlCommand("Select round(avg(RVerbruik),2) from Verbruik where ZomerWinter = 'Zomer'", connection)
                        GemZomer = CDbl(Zomer.ExecuteScalar())
                        Label8.Text = GemZomer
                        'gemiddeld verbruik winter weergeven
                        Using winter As New SqlCommand("Select round(avg(RVerbruik),2) from Verbruik where ZomerWinter = 'Winter'", connection)
                            GemWinter = CDbl(winter.ExecuteScalar())
                            Label10.Text = GemWinter
                            'berekenen tot verreden km's
                            Using km As New SqlCommand("Select sum(dagteller) from Verbruik", connection)
                                TotKm = CDbl(km.ExecuteScalar())
                                lblTotKM.Text = TotKm
                                'berekenen aantal ltr getankt
                                Using diesel As New SqlCommand("Select sum(LtrGetankt) from Verbruik", connection)
                                    TotGetankt = CDbl(diesel.ExecuteScalar())
                                    LblTotGetankt.Text = (Math.Round((TotGetankt), 2))
                                    'berekenen totaal gemiddeld verbruik
                                    TotRverbruik = (Math.Round((TotGetankt / TotKm) * 100, 2))
                                    LblGemVerbr.Text = TotRverbruik
                                    'berekenen tot gemiddeld verbruik boordComputer
                                    Using GemVerbruik As New SqlCommand("SELECT SUM(dagteller * VbkBoordcomputer) / SUM(dagteller) from Verbruik", connection)
                                        TotVerbruikBc = CDbl(GemVerbruik.ExecuteScalar())
                                        LblGemVbrBc.Text = (Math.Round((TotVerbruikBc), 2))
                                        'verschil tussen reel verbruik en verbruik boordcomputer berekenen
                                        Verschil = TotRverbruik / TotVerbruikBc - 1
                                        Dim finverschil As String = FormatPercent(Verschil)
                                        LblVerschil.Text = finverschil
                                    End Using
                                End Using
                            End Using
                        End Using
                    End Using
                    Using SqlKmStand As New SqlCommand("INSERT INTO Verbruik (kmstand, dagteller, LtrGetankt, LtrBoordComputer, RVerbruik, VbkBoordcomputer, PrijsLtr, PrijsTank, GemZomer, GemWinter, ZomerWinter, datum) VALUES (@kmstand, @dagteller, @LtrGetankt, @LtrBoordComputer, @RVerbruik, @VbkBoordcomputer, @PrijsLtr, @PrijsTank, @GemZomer, @GemWinter, @ZomerWinter, @datum)", _
                                       connection)
                        SqlKmStand.Parameters.AddWithValue("@kmstand", NewKmstand)
                        SqlKmStand.Parameters.AddWithValue("@dagteller", NewDagteller)
                        SqlKmStand.Parameters.AddWithValue("@LtrGetankt", LtrGetankt)
                        SqlKmStand.Parameters.AddWithValue("@LtrBoordComputer", ltrBoordcomputer)
                        SqlKmStand.Parameters.AddWithValue("@RVerbruik", Rverbruik)
                        SqlKmStand.Parameters.AddWithValue("@VbkBoordcomputer", vbkBoordcomputer)
                        SqlKmStand.Parameters.AddWithValue("@PrijsLtr", prijsLtr)
                        SqlKmStand.Parameters.AddWithValue("@PrijsTank", prijstank)
                        SqlKmStand.Parameters.AddWithValue("@GemZomer", AvgVerbruikZomer)
                        SqlKmStand.Parameters.AddWithValue("@GemWinter", AvgVerbruikWinter)
                        SqlKmStand.Parameters.AddWithValue("@ZomerWinter", ZomerWinter)
                        SqlKmStand.Parameters.AddWithValue("@datum", datum)
    
    
    
                        SqlKmStand.ExecuteNonQuery()
                    End Using
    
                End Using
            End Using
    
        End Sub
    
    
        Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdChart.Click
    
            grafiek.Show()
        End Sub
    End Class
    the above error that I get is linked to grafiek.show()

    this is the code for the grafics form
    Code:
    Public Class grafiek
    
        Private Sub grafiek_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'AutoDataSet1.Verbruik' table. You can move, or remove it, as needed.
            Me.VerbruikTableAdapter.Fill(Me.AutoDataSet1.Verbruik)
    
            Me.ReportViewer1.RefreshReport()
        End Sub
    
        Private Sub CmdClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdClose.Click
            Me.Close()
        End Sub
    End Class

    concerning the refresh should it help if I move this code
    Code:
                    'gemiddeld verbruik zomer weergeven
                    Using Zomer As New SqlCommand("Select round(avg(RVerbruik),2) from Verbruik where ZomerWinter = 'Zomer'", connection)
                        GemZomer = CDbl(Zomer.ExecuteScalar())
                        Label8.Text = GemZomer
                        'gemiddeld verbruik winter weergeven
                        Using winter As New SqlCommand("Select round(avg(RVerbruik),2) from Verbruik where ZomerWinter = 'Winter'", connection)
                            GemWinter = CDbl(winter.ExecuteScalar())
                            Label10.Text = GemWinter
                            'berekenen tot verreden km's
                            Using km As New SqlCommand("Select sum(dagteller) from Verbruik", connection)
                                TotKm = CDbl(km.ExecuteScalar())
                                lblTotKM.Text = TotKm
                                'berekenen aantal ltr getankt
                                Using diesel As New SqlCommand("Select sum(LtrGetankt) from Verbruik", connection)
                                    TotGetankt = CDbl(diesel.ExecuteScalar())
                                    LblTotGetankt.Text = (Math.Round((TotGetankt), 2))
                                    'berekenen totaal gemiddeld verbruik
                                    TotRverbruik = (Math.Round((TotGetankt / TotKm) * 100, 2))
                                    LblGemVerbr.Text = TotRverbruik
                                    'berekenen tot gemiddeld verbruik boordComputer
                                    Using GemVerbruik As New SqlCommand("SELECT SUM(dagteller * VbkBoordcomputer) / SUM(dagteller) from Verbruik", connection)
                                        TotVerbruikBc = CDbl(GemVerbruik.ExecuteScalar())
                                        LblGemVbrBc.Text = (Math.Round((TotVerbruikBc), 2))
                                        'verschil tussen reel verbruik en verbruik boordcomputer berekenen
                                        Verschil = TotRverbruik / TotVerbruikBc - 1
                                        Dim finverschil As String = FormatPercent(Verschil)
                                        LblVerschil.Text = finverschil
                                    End Using
                                End Using
                            End Using
                        End Using
                    End Using
    Below the part where I do my insert in the database?
    Last edited by paulus4605; Apr 6th, 2013 at 01:59 AM.

  33. #33

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    40

    Re: how to calculate with figures from the database

    I have tried several options that I found on the internet to try ans solve this but still without any luck can someone help me out here with this error : Attempted to read or write protected memory. This is often an indication that other memory is corrupt."

  34. #34

    Thread Starter
    Member
    Join Date
    Mar 2013
    Posts
    40

    Re: how to calculate with figures from the database

    anyone that can give me a hand here?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width