VB.net - getting data from excel spreadsheet for textboxes

I really hope I ask this question right. I’m, working on a vb.net application that uses excel as a database. I have dropdown box that selects a person’s name. The name is an exact match to a column in the excel database. I have the database connection setup and its working great.

The trouble I’m having is trying to get the data for that person into the textboxes in my application. All the data for the person is on the same row as the person’s name… example. The person Halle Berry name is on A2 in the excel spread sheet and all her data is on B2, C2, D2 and E2.

Private Function CreateConnString(ByVal Str As String) As String
        Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Str + ";Extended Properties=""Excel 8.0;HDR=YES;User ID=user01;Password=ThePassword;"""
    End Function

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim Conn As New OleDbConnection
        Dim Comm As New OleDbCommand
        Dim DReader As OleDbDataReader
        Dim Str As String = ""
        Dim Ename = EmpName.Text
        Conn.ConnectionString = CreateConnString("c:\data\TimeData.xls")
        Comm.Connection = Conn
        Comm.CommandText = "Select" + Ename + "From [Einfo$]"""

    End Sub

Open in new window


In my code above I have it going to the Einfo sheet and selecting the employee name (Ename). How do i tell it to read the data on it's row and put it in the textboxes?
LVL 1
AnthonySmithMCPAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KimputerCommented:
Just a sample code, so you can continue your own code. This assumes you have a sheet called Einfo, where the header (probably cell A1) is name Ename, it will message you all the following columns (this time only 3, you can figure out the rest).


    Private Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        Dim Conn As New OleDbConnection
        Dim Comm As New OleDbCommand
        Dim DReader As OleDbDataReader
        Dim Str As String = ""
        Dim Ename = EmpName.Text
        Conn.ConnectionString = CreateConnString("c:\data\TimeData.xls")
        Conn.Open()
        Comm.Connection = Conn
        Comm.CommandText = "SELECT * FROM [Einfo$] where Ename= """ & Ename & """"
        DReader = Comm.ExecuteReader(CommandBehavior.CloseConnection)
        Try
            While (DReader.Read)
                MsgBox(DReader.Item(1).ToString & " - " & DReader.Item(2).ToString & " - " & DReader.Item(3).ToString)

            End While
        Finally
            DReader.Close()
        End Try
    End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AnthonySmithMCPAuthor Commented:
I'll try this when i get home.. Also the first row in my spreadsheet are titles.  Ename is the textbox in my application (EmpName.Text) that has the user name. But i think this is what I'm looking for.
0
jyothi kunaCommented:
getting error at  Conn.Open()
and here only one textbox is there where is the remaining   textbox and  from where u  get that connection string
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.