I am using vb.net to connect to mysql database and I parse the data to a datagridview and then I do some calculations and I create 2 extra columns to hold the calculations data. People told me that this is not a good practice so instead I must do the calculations in the datatable and then parse the data to datagridview. I searched for solutions and tried different stuff but I can't make it work. Does anyone knows how can I do that?
Here Is the code I am using so far.
<pre> Imports MySql.Data.MySqlClient
Public Class Form1
Dim MysqlConn As MySqlConnection
Dim COMMAND As MySqlCommand
Public sconnection As New MySqlConnection
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
If sconnection.State = ConnectionState.Closed Then
sconnection.ConnectionString = "server=sql11.freesqldatabase.com;userid=sql11508072;password=s2PWmyaDkE"
sconnection.Open()
End If
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
MysqlConn = New MySqlConnection
MysqlConn.ConnectionString = "server=sql11.freesqldatabase.com;userid=sql11508072;password=s2PWmyaDkE"
Dim SDA As New MySqlDataAdapter
Dim dbdataset As New DataTable
Dim bsource As New BindingSource
dbdataset.Clear()
DataGridView1.Columns.Clear()
DataGridView1.Refresh()
Try
MysqlConn.Open()
Dim query As String
query = ("select * from sql11508072.Complete ")
COMMAND = New MySqlCommand(query, MysqlConn)
SDA.SelectCommand = COMMAND
SDA.Fill(dbdataset)
bsource.DataSource = dbdataset
DataGridView1.DataSource = bsource
SDA.Update(dbdataset)
MysqlConn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
MysqlConn.Dispose()
End Try
DataGridView1.Columns.Add("NameOfColumn", "First Calc Test")
DataGridView1.Columns.Add("NameOfColumn", "Second Calc Test")
Try
For i As Integer = DataGridView1.RowCount - 1 - 1 To 0 Step -1
If CInt(Replace(DataGridView1.Rows(i).Cells(5).Value, ",", "")) > 2 Then
Me.DataGridView1.Rows(i).Cells(8).Value = "High"
ElseIf CInt(Replace(DataGridView1.Rows(i).Cells(5).Value, ",", "")) > 1 Then
Me.DataGridView1.Rows(i).Cells(8).Value = "Medium"
ElseIf CInt(Replace(DataGridView1.Rows(i).Cells(5).Value, ",", "")) > 0 Then
Me.DataGridView1.Rows(i).Cells(8).Value = "Low"
ElseIf CInt(Replace(DataGridView1.Rows(i).Cells(5).Value, ",", "")) < 1 Then
Me.DataGridView1.Rows(i).Cells(8).Value = "Very Low"
End If
If CInt(Replace(DataGridView1.Rows(i).Cells(7).Value, ",", "")) - CInt(Replace(DataGridView1.Rows(i).Cells(5).Value, ",", "")) > 2 Then
Me.DataGridView1.Rows(i).Cells(9).Value = "High"
ElseIf CInt(Replace(DataGridView1.Rows(i).Cells(7).Value, ",", "")) - CInt(Replace(DataGridView1.Rows(i).Cells(5).Value, ",", "")) > 1 Then
Me.DataGridView1.Rows(i).Cells(9).Value = "Medium"
ElseIf CInt(Replace(DataGridView1.Rows(i).Cells(7).Value, ",", "")) - CInt(Replace(DataGridView1.Rows(i).Cells(5).Value, ",", "")) > 0 Then
Me.DataGridView1.Rows(i).Cells(9).Value = "Low"
ElseIf CInt(Replace(DataGridView1.Rows(i).Cells(7).Value, ",", "")) - CInt(Replace(DataGridView1.Rows(i).Cells(5).Value, ",", "")) < 1 Then
Me.DataGridView1.Rows(i).Cells(9).Value = "Very Low"
End If
Next
Catch
End Try
End Sub
End Class
What I have tried:
I tried some different code but I don't have anything that is usable.