?
Solved

VSTO populating cells with datatable

Posted on 2010-08-18
6
Medium Priority
?
740 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 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

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!
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
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…

621 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