Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VSTO populating cells with datatable

Posted on 2010-08-18
6
Medium Priority
?
737 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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…

609 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