Solved

VSTO populating cells with datatable

Posted on 2010-08-18
6
696 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
Comment Utility
0
 

Author Comment

by:NerishaB
Comment Utility
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
Comment Utility
Fill to a dataset.
Change line 48 from
myDataAdapter.Fill(myData)
to
myDataAdapter.Fill(Oztech_testDataSet)
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:NerishaB
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

728 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