asp.net - MS Access OLEDBConnection to Excel Issue -

so have searched 2 days , while can find lot of examples of how excel worksheet gridview, none of them work me.

this goal: need read excel file, has 1 worksheet in , should 1 worksheet in it, gridview in asp.net website , using vb.net in code behind file.

i tried 1 way trying use schema of table name (to sheet names) no matter sheet name came 'algrip' of there no sheet name in of workbooks testing with.

so scrapped , using code: (which job done, sort of)

    'setup variables     dim xlconnstr string = ""     dim filename string = path.getfilename(fileupload1.postedfile.filename)     dim extension string = path.getextension(fileupload1.postedfile.filename)     dim folderpath string = configurationmanager.appsettings("folderpath")     dim sheetname string = inputbox("enter sheetname: ", "excel worksheet name")      'adjust sheetname     sheetname = sheetname + "$"      'set connection based on excel file extension     select case extension         case ".xls"             'excel  97-03             xlconnstr = configurationmanager.connectionstrings("excel03constring").connectionstring         case ".xlsx"             'excel 07-forward             xlconnstr = configurationmanager.connectionstrings("excel07constring").connectionstring     end select      xlconnstr = string.format(xlconnstr, folderpath & filename)     dim connxl new oledb.oledbconnection(xlconnstr)     pnlfileinfo.visible = true     connxl.open()     using xlcmd new oledbcommand         xlcmd.commandtype = commandtype.text         xlcmd.commandtext = ("select * [" + sheetname + "]")         xlcmd.connection = connxl         using xlds new dataset()             using xlda new oledbdataadapter(xlcmd)                 xlda.fill(xlds)                 gvexcelfile.datasource = xlds                 gvexcelfile.databind()             end using         end using     end using     connxl.close() 

now problem this; on follwoing line:

xlcmd.commandtext = ("select * [" + sheetname + "]")

if not have left , right brackets, because sheet names have spaces in them, query error. if add brackets prompts me twice sheet name. life of me can not figure out why.

i have put break point in on line listed above , checked value of variable sheetname , correct reason prompted again.

does have idea why doing this? missing? ideally wanted able read sheet name , feed line select statement there no user action required ever got way same bad sheet name 'algrip'.

the excel sheet xls file can saved xlsx if help.

i open re-doing code if can read sheet name dynamically.

thanks help!!

try storing whole command in variable assign xlcmd.commandtext using oledbcommand constructor

dim sheetname string = inputbox("enter sheetname: ", "excel worksheet name") sheetname = sheetname & "$" dim strquery string = "select * [" & sheetname & "]"  ...  using xlcmd new oledbcommand(strquery ,connxl)      using xlds new dataset()         using xlda new oledbdataadapter(xlcmd)             xlda.fill(xlds)             gvexcelfile.datasource = xlds             gvexcelfile.databind()         end using     end using  end using connxl.close()