Click here to Skip to main content
14,391,319 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have to import excel and read the first column of the excel and search.

I've successfully import the data and now trying to read the first column.


my gridview:
---------
Chair_id
---------
chair01
chair02
chair03

Then i need to run query based on my chair_id
Result
--------------------------------------------
Chair_Id | Chair colour | Chair weight (Kg)
--------------------------------------------
chair01 | red | 12.2
chair01 | red | 12.3
chair02 | red | 1.2
chair02 | blue | 1.11
chair03 | yellow | 1.0

What I have tried:

 Dim connString As String = ""
                Dim strFileType As String = Path.GetExtension(FileUpload1.FileName).ToLower()
                Dim path__1 As String = FileUpload1.PostedFile.FileName
                'Connection String to Excel Workbook
                If strFileType.Trim() = ".xls" Then
                    connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path__1 & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
                ElseIf strFileType.Trim() = ".xlsx" Then
                    connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path__1 & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""
                End If

                Dim ds As New DataSet
                ds.Tables.Add("M_DATAEXCEL")
                Dim dtexcel As New DataTable
                Dim query As String = "SELECT [chair_id] FROM [Sheet1$]"
                Dim conn As New OleDbConnection(connString)
                If conn.State = ConnectionState.Closed Then
                    conn.Open()
                End If
                Dim cmd As New OleDbCommand(query, conn)
                Dim da As New OleDbDataAdapter(cmd)
                'Dim ds As New DataSet()
                da.Fill(ds.Tables("M_DATAEXCEL"))
                dtexcel = ds.Tables("M_DATAEXCEL")
                GridView1.DataSource = ds.Tables("M_DATAEXCEL")
                GridView1.DataBind()
                da.Dispose()
                conn.Close()
                conn.Dispose()
 
                For i As Integer = 0 To dtexcel.Rows.Count - 1

.
.
.
.
.
       i'm stuck what to do next. T__T
Posted
Updated 18-Aug-19 6:21am
Comments
OriginalGriff 18-Aug-19 10:46am
   
And?
What have you tried?
Where are you stuck?
What help do you need?
VB_newbie11 18-Aug-19 10:54am
   
im stuck at retrieving/read the column. ive tried dt.row(i)(chair_id) but it doesnt read it.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Quote:
im stuck at retrieving/read the column. ive tried dt.row(i)(chair_id) but it doesnt read it.

So why not:
0) Use the actual name of the DataTable in your loop instead of "dt": dtexcel
1) Use the right property of the DataTable: Rows, not row
2) Use the column name as a string instead of a variable name

Dim row as DataRow = dtexcel.Rows(i)
Dim value = row("Char_Id")
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100