Link to home
Start Free TrialLog in
Avatar of wayneray
waynerayFlag for United States of America

asked on

Want to Disply Multiple Rows in Text Box from Excel Workbook using VB.Net

I am writing a desktop application using VB.Net and an Excel Workbook as the database. I can display one row easily using the code below.  How do I display all rows from the workbook that have the same date? Thanks in advance.

Here is the code I am using. I have attached the Excel File.

Private m_sConn1 As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
               "Data Source=c:\computers\vb project\Sweetheart\mydates.xlsx;" & _
               "Extended Properties=""Excel 12.0;HDR=YES"""


    Private Sub cmdFind_Click(sender As Object, e As EventArgs) Handles cmdFind.Click
        Dim cn As New System.Data.OleDb.OleDbConnection(m_sConn1)
        Dim appt, apdt, amessage As String
        Dim format = "MM,dd,yyyy"

        Dim begindt As Date = dtpBegin.Value.ToString(format)
        Dim enddt As Date = dtpEndDt.Value.ToString(format)
        Dim cm As New OleDbDataAdapter("Select * From [Sheet1$] where Date =  '" & begindt & "'", cn)
       
        Dim ds As DataSet = New DataSet()


        cn.Open()
        cm.Fill(ds)
        If TextBox1.Text = "" Then
            Debug.WriteLine(vbCrLf & "Bills:" & vbCrLf & "=============")
            Dim dr As DataRow


            For Each dr In ds.Tables(0).Rows 'Show results in output window

                'Debug.WriteLine(System.String.Format("{0,-15}{1, -6}{2}", _
                '       dr("Event"), dr("Date"), dr("Message")))

            Next
            appt = dr("Event")
            apdt = dr("Date")
            amessage = dr("Message")
            TextBox1.Text = "Category      " & "Date             " & "Info  " & vbCrLf & vbCrLf & appt & "    " & apdt & "    " & amessage
        End If





    End Sub



mydates.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wayneray

ASKER

Thanks Scott. I know I could use a datagrid and I have already experienced the spacing issues. I haven't tried a listview. Thanks again.
Scott's solution helped me overcome a block in the road. His response offered me more than I asked for and I appreciate his answer.