Solved

MySQL VSTO reading from dataTable

Posted on 2010-08-20
5
564 Views
Last Modified: 2013-11-10
Hi, i have created a dataTable, and have tested the query, and it  diaplys a result.  If you see the code I have attached below:
When is step throught the code, it executes the sql statement, fills the datatable, but after the lines 81-82:
  nBFBal = nBFBal + myRow.Item("Amount").ToString()
  amount = myRow.Item("Amount").ToString() + myRow("PaidAmont").ToString(), it stops going further and exeutes the "Finally" statement.  Any ideas what is going wrong here?
Private Sub btnCreateStatement_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateStatement.Click



        Dim myConn As New MySqlConnection

        Dim myComm As New MySqlCommand

        Dim myAdapter As New MySqlDataAdapter

        Dim myData As New DataTable

        Dim strSQL As String



        Dim sEndDate As String

        Dim CustomerRef As String

        Dim current As Double

        Dim thirty As Double

        Dim sixty As Double

        Dim ninety As Double

        Dim onetwenty As Double

        Dim amount As Double

        Dim unallocated As Double

        Dim nBFBal As Double

        Dim nCFBal As Double



  CustomerRef = CustomerRefComboBox.Text

  sEndDate = Format(DateTimePicker2.Value, "yyyy-MM-dd")



  strSQL = " SELECT tr.TransID, tr.Date, trt.Category, trt.Descr, cz.CustomerRef, tr.Amount, SUM( tr.Amount ) AS TotalGroup, tr.Notes, " & _

"PERIOD_DIFF(CONCAT(YEAR('" & sEndDate & "'),IF(MONTH('" & sEndDate & "')<10,'0',''),MONTH('" & sEndDate & "')),CONCAT(YEAR(tr.Date),IF(MONTH(tr.Date)<10,'0',''),MONTH(tr.Date))) AS Days, " & _

"IFNULL( (Select SUM(AllocationAmount)FROM Transactions T1 LEFT JOIN TransactionAllocations TA ON TA.TransactionID = T1.TransID " & _

"LEFT JOIN Transactions T2 ON T2.TransID = TA.TransactionID_Allocation WHERE(tr.TransID = T1.TransID) AND T2.CustomerID = '3' ) * -1, 0) AS TotalAgainstCustomer, " & _

"IFNULL( (Select SUM(AllocationAmount)FROM Transactions T1 LEFT JOIN TransactionAllocations TA ON TA.TransactionID_Allocation = T1.TransID " & _

"WHERE tr.TransID = T1.TransID) * -1, 0) AS PaidAmount " & _

"FROM Customers cz, Transactions tr, TransTypes trt " & _

"WHERE (tr.CustomerID = cz.CustomerID AND cz.CustomerRef = '" & CustomerRef & "' AND tr.TransTypeID = trt.TransTypeID) " & _

"AND (tr.Date<='" & sEndDate & "') " & _

"AND NOT tr.TransTypeID IN ('RESOLVE DEBIT', 'RESOLVE CREDIT') " & _

"GROUP BY IFNULL( LinkTo, TransID ) " & _

"HAVING TotalGroup <>0 " & _

"ORDER BY tr.Date, tr.TransID LIMIT 0, 30"



   myConn = GetConnection()     

   Try

      myConn.Open()

        Try

           myComm.Connection = myConn

           myComm.CommandText = strSQL



           myAdapter.SelectCommand = myComm

           myAdapter.Fill(myData)



           DataGridView1.DataSource = myData

           Dim myRow As DataRow

             For Each myRow In myData.Rows

                 Dim myCol As DataColumn

                 For Each myCol In myData.Columns

                     nBFBal = 0



                    If Convert.ToDateTime(myRow.Item("Date")) >= DateTimePicker1.Value Then

                    If myRow.Item("TotalAgainstCustomer").ToString() <> 0 Then

                          nBFBal = nBFBal + Convert.ToDouble(myRow.Item("Amount").ToString())

                     Else

                           nBFBal = nBFBal + myRow.Item("Amount").ToString()

                           amount = myRow.Item("Amount").ToString() + myRow("PaidAmont").ToString()

                     End If



         If myRow.Item("Days").ToString() <= 0 Then

                current = current + amount

         ElseIf myRow.Item("Days").ToString() = 1 Then

                 thirty = thirty + amount

         ElseIf myRow.Item("Days").ToString = 2 Then

                 sixty = sixty + amount

          ElseIf myRow.Item("Days").ToString() = 3 Then

                 ninety = ninety + amount

          ElseIf myRow.Item("Days").ToString() = 4 Then

                 onetwenty = onetwenty = amount

          End If

       End If

       Next

          nCFBal = nBFBal

          If myRow.Item("Date") < DateTimePicker2.Value Then

             Globals.Sheet64.TransactionDate.Value = myRow.Item("Date").ToString

             Globals.Sheet64.TransactionType.Value = myRow.Item("Category").ToString

             Globals.Sheet64.TransDescription.Value = myRow.Item("Descr").ToString

             Globals.Sheet64.TransDocument.Value = myRow.Item("Notes").ToString

             If myRow.Item("TotalAgainstCustomer") <> 0 Then

                 Globals.Sheet64.stm_amount.Value = myRow.Item("Amount").ToString

                  nCFBal = nCFBal + Convert.ToDouble(myRow.Item("Amount").ToString())

                  amount = Convert.ToDouble(myRow.Item("Amount").ToString()) - Convert.ToDouble(myRow.Item("TotalAgainstCustomer").ToString())

              Else

                 Globals.Sheet64.stm_amount.Value = myRow.Item("Amount").ToString

                 nCFBal = nCFBal + Convert.ToDouble(myRow.Item("Amount").ToString())

                 amount = Convert.ToDouble(myRow.Item("Amount").ToString()) - Convert.ToDouble(myRow.Item("PaidAmount").ToString())

              End If

                    End If

                Next

            Catch myError As MySqlException

       MessageBox.Show("There was an error reading from the database: " & myError.Message)

            End Try

            Next

        Catch myError As MySqlException

            MessageBox.Show("Error connecting to the database: " & myError.Message)

        Finally

            If myConn.State <> ConnectionState.Closed Then

                myConn.Close()

            End If

        End Try

    End Sub

Open in new window

Private Sub btnCreateStatement_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateStatement.Click



        Dim myConn As New MySqlConnection

        Dim myComm As New MySqlCommand

        Dim myAdapter As New MySqlDataAdapter

        Dim myData As New DataTable

        Dim strSQL As String



        Dim sEndDate As String

        Dim CustomerRef As String

        Dim current As Double

        Dim thirty As Double

        Dim sixty As Double

        Dim ninety As Double

        Dim onetwenty As Double

        Dim amount As Double

        Dim unallocated As Double

        Dim nBFBal As Double

        Dim nCFBal As Double



  CustomerRef = CustomerRefComboBox.Text

  sEndDate = Format(DateTimePicker2.Value, "yyyy-MM-dd")



  strSQL = " SELECT tr.TransID, tr.Date, trt.Category, trt.Descr, cz.CustomerRef, tr.Amount, SUM( tr.Amount ) AS TotalGroup, tr.Notes, " & _

"PERIOD_DIFF(CONCAT(YEAR('" & sEndDate & "'),IF(MONTH('" & sEndDate & "')<10,'0',''),MONTH('" & sEndDate & "')),CONCAT(YEAR(tr.Date),IF(MONTH(tr.Date)<10,'0',''),MONTH(tr.Date))) AS Days, " & _

"IFNULL( (Select SUM(AllocationAmount)FROM Transactions T1 LEFT JOIN TransactionAllocations TA ON TA.TransactionID = T1.TransID " & _

"LEFT JOIN Transactions T2 ON T2.TransID = TA.TransactionID_Allocation WHERE(tr.TransID = T1.TransID) AND T2.CustomerID = '3' ) * -1, 0) AS TotalAgainstCustomer, " & _

"IFNULL( (Select SUM(AllocationAmount)FROM Transactions T1 LEFT JOIN TransactionAllocations TA ON TA.TransactionID_Allocation = T1.TransID " & _

"WHERE tr.TransID = T1.TransID) * -1, 0) AS PaidAmount " & _

"FROM Customers cz, Transactions tr, TransTypes trt " & _

"WHERE (tr.CustomerID = cz.CustomerID AND cz.CustomerRef = '" & CustomerRef & "' AND tr.TransTypeID = trt.TransTypeID) " & _

"AND (tr.Date<='" & sEndDate & "') " & _

"AND NOT tr.TransTypeID IN ('RESOLVE DEBIT', 'RESOLVE CREDIT') " & _

"GROUP BY IFNULL( LinkTo, TransID ) " & _

"HAVING TotalGroup <>0 " & _

"ORDER BY tr.Date, tr.TransID LIMIT 0, 30"



   myConn = GetConnection()     

   Try

      myConn.Open()

        Try

           myComm.Connection = myConn

           myComm.CommandText = strSQL



           myAdapter.SelectCommand = myComm

           myAdapter.Fill(myData)



           DataGridView1.DataSource = myData

           Dim myRow As DataRow

             For Each myRow In myData.Rows

                 Dim myCol As DataColumn

                 For Each myCol In myData.Columns

                     nBFBal = 0



                    If Convert.ToDateTime(myRow.Item("Date")) >= DateTimePicker1.Value Then

                    If myRow.Item("TotalAgainstCustomer").ToString() <> 0 Then

                          nBFBal = nBFBal + Convert.ToDouble(myRow.Item("Amount").ToString())

                     Else

                           nBFBal = nBFBal + myRow.Item("Amount").ToString()

                           amount = myRow.Item("Amount").ToString() + myRow("PaidAmont").ToString()

                     End If



         If myRow.Item("Days").ToString() <= 0 Then

                current = current + amount

         ElseIf myRow.Item("Days").ToString() = 1 Then

                 thirty = thirty + amount

         ElseIf myRow.Item("Days").ToString = 2 Then

                 sixty = sixty + amount

          ElseIf myRow.Item("Days").ToString() = 3 Then

                 ninety = ninety + amount

          ElseIf myRow.Item("Days").ToString() = 4 Then

                 onetwenty = onetwenty = amount

          End If

       End If

       Next

          nCFBal = nBFBal

          If myRow.Item("Date") < DateTimePicker2.Value Then

             Globals.Sheet64.TransactionDate.Value = myRow.Item("Date").ToString

             Globals.Sheet64.TransactionType.Value = myRow.Item("Category").ToString

             Globals.Sheet64.TransDescription.Value = myRow.Item("Descr").ToString

             Globals.Sheet64.TransDocument.Value = myRow.Item("Notes").ToString

             If myRow.Item("TotalAgainstCustomer") <> 0 Then

                 Globals.Sheet64.stm_amount.Value = myRow.Item("Amount").ToString

                  nCFBal = nCFBal + Convert.ToDouble(myRow.Item("Amount").ToString())

                  amount = Convert.ToDouble(myRow.Item("Amount").ToString()) - Convert.ToDouble(myRow.Item("TotalAgainstCustomer").ToString())

              Else

                 Globals.Sheet64.stm_amount.Value = myRow.Item("Amount").ToString

                 nCFBal = nCFBal + Convert.ToDouble(myRow.Item("Amount").ToString())

                 amount = Convert.ToDouble(myRow.Item("Amount").ToString()) - Convert.ToDouble(myRow.Item("PaidAmount").ToString())

              End If

                    End If

                Next

            Catch myError As MySqlException

       MessageBox.Show("There was an error reading from the database: " & myError.Message)

            End Try

            Next

        Catch myError As MySqlException

            MessageBox.Show("Error connecting to the database: " & myError.Message)

        Finally

            If myConn.State <> ConnectionState.Closed Then

                myConn.Close()

            End If

        End Try

    End Sub

Open in new window

0
Comment
Question by:NerishaB
  • 3
  • 2
5 Comments
 
LVL 53

Expert Comment

by:Dhaest
Comment Utility
What is the exact errormessage you are getting ? Perhaps there is an incorrect value (null or spaces, ...)

I also assume that you want to add double values

Amount = Double.Parse(myRow.Item("Amount").ToString()) + Double.Parse(myRow("PaidAmont").ToString())
0
 

Author Comment

by:NerishaB
Comment Utility
I'm not getting any error message. The problem is that after it runs line 60, it does not execute the next "if" statement which is :
 If myRow.Item("Days").ToString() <= 0 Then
It merely moves out of the lop to the "Finally statement.
0
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 total points
Comment Utility
>> I'm not getting any error message

Your exception handling is not good ...
Try this:

Try
....

Catch myError As MySqlException
       MessageBox.Show("There was an error reading from the database: " & myError.Message)
Catch myError As MySqlException
            MessageBox.Show("Error connecting to the database: " & myError.Message)
Catch myError As Exception
           MessageBox.Show(myError.toString())
finally
...
0
 
LVL 53

Expert Comment

by:Dhaest
Comment Utility
Another remark. You are converting everything into a string, and then perform sums, .... ?
0
 

Author Closing Comment

by:NerishaB
Comment Utility
Thanks, it works!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

771 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