Getting more than one data line while using commandtext (Visual Basic.Net)

Good Evening,

I'm trying to work through this application. I'm new to programming and need some help. I'm building a VB.net application that connects to an excel file as a database. When using the attached code... It goes into my database gets some data from one of the sheets/ Rows then shows it in message boxes (I'll do other stuff with it later). That works fine.

My problem is I want it to also grab another row of data from the same sheet and display that data also.

Please help

    Private Sub STapply_Click(sender As Object, e As EventArgs) Handles STapply.Click


        Dim Site = STsiteComboBox.Text
        Dim Team1 = STteam1ComboBox.Text
        Dim Team2 = STteam2ComboBox.Text
        Dim NDS = STndsteamComboBox.Text
        HL_site_View.Text = Site

        Dim Conn As New OleDbConnection
        Dim Comm As New OleDbCommand
        Dim DReader_T1L As OleDbDataReader

        Dim Str As String = ""


        Conn.ConnectionString = CreateConnString("c:\tempTestDB.xls")
        Conn.Open()
        Comm.Connection = Conn
        Comm.CommandText = "SELECT * FROM [TeamsTL$] where Teams= """ & Team1 & """"
        DReader_T1L = Comm.ExecuteReader(CommandBehavior.CloseConnection)


        Try
            While (DReader_T1L.Read)

                MessageBox.Show(DReader_T1L.Item(1))
                MessageBox.Show(DReader_T1L.Item(2))
                MessageBox.Show(DReader_T1L.Item(3))
                MessageBox.Show(DReader_T1L.Item(4))

            End While
        Finally
            DReader_T1L.Close()
        End Try

    End Sub

Open in new window

LVL 1
AnthonySmithMCPAsked:
Who is Participating?
 
Fernando SotoRetiredCommented:
Hi AnthonySmithMCP;

This line of code will return all records / Line from the excel work sheet.

Comm.CommandText = "SELECT * FROM [TeamsTL$]

Because you have this line of code in your program

Comm.CommandText = "SELECT * FROM [TeamsTL$] where Teams= """ & Team1 & """"

You are filtering the lines for those lines who's Teams column is empty and who's Team1 column is also empty but Team1 does not exist in the excel sheet so no records will be returned.

To get multiple lines to be returned you need to filter that code line above such that it picks those two lines that you want.
0
 
Fernando SotoRetiredCommented:
Can you post the execl file and tell us what item you need to get from the sheet as well.
0
 
AnthonySmithMCPAuthor Commented:
Attached
tempTestDB.xls
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.