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.
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
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
Re: how to calculate with figures from the database
Originally Posted by paulus4605
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
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.
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.
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
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.
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.
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.
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.
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.
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.
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
Re: how to calculate with figures from the database
Originally Posted by paulus4605
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.
Originally Posted by paulus4605
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.
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.
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.
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
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.
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.
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
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.
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
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.
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."