Solved

VSTO populating cells with datatable

Posted on 2010-08-18
6
713 Views
Last Modified: 2013-11-10
Hi,

I have an excel template created in VSTO, and I have added an Action Pane with a combobox that is bound to the "Customers" table referencing the "CustomerRef" field.  Now, what I want, is to have the user selet a particular customer from the drop down list, then to display the data, as seen in the query below on my spreadsheet,  I have named values where I want the reulst displayed, but am at a loss on how to extract the data from the data table.  Please see code below on what I have thus far.  Can anyone help me in completing this to display the results on my sheet?
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 myDataAdapter As New MySqlDataAdapter
        Dim myData As New DataTable
        Dim CustomersDataRow As oztech_testDataSet.CustomersRow = CType(CType(Me.CustomersBindingSource.Current, DataRowView).Row(), oztech_testDataSet.CustomersRow)

        Dim strSQL As String
        Dim sEndDate As String
        Dim CustomerRef As String

        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 = '14' ) * -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

                myDataAdapter.SelectCommand = myComm
                myDataAdapter.Fill(myData)

            Catch myError As MySqlException
                MessageBox.Show("There was an error reading from the database: " & myError.Message)
            End Try

        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

End Class

Open in new window

0
Comment
Question by:NerishaB
  • 3
  • 2
6 Comments
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33464878
0
 

Author Comment

by:NerishaB
ID: 33464981
Hi,

Thank you.  I have added more code to read the data table, but the problem now is that, when I put a breakpoint and try to run this, it stops at this line:
 myDataAdapter.Fill(myData)
I dont get an error, it just never goes beyond this point.
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 myDataAdapter 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 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 = '14' ) * -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

                myDataAdapter.SelectCommand = myComm
                myDataAdapter.Fill(myData)
                myConn.Close()

                For Each myData In Oztech_testDataSet.Tables
                    Dim myRow As DataRow
                    For Each myRow In myData.Rows
                        Dim myCol As DataColumn
                        For Each myCol In myData.Columns

                            nBFBal = 0
                            If myRow("Date").ToString() >= DateTimePicker1.Value Then
                                If myRow("TotalAgainstCustomer").ToString() <> 0 Then
                                    nBFBal = nBFBal + myRow("Amount").ToString()
                                Else
                                    nBFBal = nBFBal + myRow("Amount").ToString()
                                    amount = myRow("Amount").ToString() + myRow("PaidAmont").ToString()
                                End If
                                If myRow("Days").ToString() <= 0 Then
                                    current = current + amount
                                ElseIf myRow("Days").ToString() = 1 Then
                                    thirty = thirty + amount
                                ElseIf myRow("Days").ToString = 2 Then
                                    sixty = sixty + amount
                                ElseIf myRow("Days").ToString() = 3 Then
                                    ninety = ninety + amount
                                ElseIf myRow("Days").ToString() = 4 Then
                                    onetwenty = onetwenty = amount
                                End If
                            End If
                        Next
                        nCFBal = nBFBal

                    Next

                Next

            Catch myError As MySqlException
                MessageBox.Show("There was an error reading from the database: " & myError.Message)
            End Try

        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

End Class

Open in new window

0
 
LVL 35

Expert Comment

by:Miguel Oz
ID: 33472397
Fill to a dataset.
Change line 48 from
myDataAdapter.Fill(myData)
to
myDataAdapter.Fill(Oztech_testDataSet)
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:NerishaB
ID: 33472499
Ok, I have changed line 48.  When I run through the code, after it executes Line 48, it skips everything else and goes into the "Finally" statement.  What else am I doing wrong?
0
 
LVL 35

Accepted Solution

by:
Miguel Oz earned 500 total points
ID: 33480876
tips:
1) Is SQL statement correct? try at SQL server o a small prototype winform.
2)
2) Is Oztech_testDataSet of type DataSet, valid instance and not been filled or used elsewhere in your code.
If you are using it for something else just create a new one.
3) Also delete line 49, you are doing that on the finally statement anyway.
4) You do not need line 6 ( Dim myData As New DataTable) the for each loop of line 51 will set it up for you.
0
 

Author Closing Comment

by:NerishaB
ID: 33482380
Ok, I have checked the SQL, and there was a problem wth the date that was being read in.  I fixed it now, and it is reading the datatable.  Thanks for your help.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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…

831 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