?
Solved

VSTO populating cells with datatable

Posted on 2010-08-18
6
Medium Priority
?
730 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 36

Expert Comment

by:Miguel Oz
ID: 33472397
Fill to a dataset.
Change line 48 from
myDataAdapter.Fill(myData)
to
myDataAdapter.Fill(Oztech_testDataSet)
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 36

Accepted Solution

by:
Miguel Oz earned 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

801 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