Problem in importing data from excel to datagrid in web application...

[Balagurunathan S]

Hi Friends... I am importing data from excel file to a datagrid.I use the following code to do that.But i get "Unspecified error" when i open the oledb connection.Wat could be the problem.Someone help in this regard...The code is given below... Private Sub btnClick_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClick.Click Try Dim filepath As String = System.IO.Path.GetFullPath(Me.FindFile.PostedFile.FileName) GrdExcel.DataSource = GetExcelData(filepath) GrdExcel.DataBind() Catch ex As Exception Response.Write(ex.Message) End Try End Sub Private Function GetExcelData(ByVal strpath As String) As DataSet Try Dim ExcelConnection As String = "" ExcelConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strpath & ";" & "Extended Properties=""Excel 8.0;HDR=Yes""" Dim strExcelConn As String = ExcelConnection Dim dbConn As New Data.OleDb.OleDbConnection(strExcelConn) Dim strSQL As String Dim delimeter As Char() = {"\"} Dim splitter As String() = strpath.Split(delimeter) Dim file As String = splitter(splitter.Length - 1) strSQL = "SELECT * FROM [" & file & "$]" dbConn.Open() Dim cmd As New Data.OleDb.OleDbCommand(strSQL, dbConn) Dim dsExcel As New DataSet Dim daExcel As New Data.OleDb.OleDbDataAdapter(cmd) daExcel.Fill(dsExcel) dbConn.Close() Return dsExcel Catch ex As Exception Response.Write(ex.Message) End Try End Function

Balaguru

[Christian Graus]

The problem is you don't understand what ASP.NET is, at all. You get a filepath from your client, then you try to read the file on the server. Unless the server and client are the same machine, that isn't going to work, the file just isn't there. SAve the file to the server file system in order to read it. Of course, this presents it's own problems, if you have more than one user at once and the file names clash, you'll be in trouble. Use Path.GetTempFileName ( something like that ) to work around that, or just File.Exists when you're saving it.

Christian Graus Please read this if you don't understand the answer I've given you "also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )

[Balagurunathan S]

Dear Christian Graus, Thx for ur reply..The thing is..the .csv/excel file resides only in the client machine.The user has to select the .csv/excel file from his machine and then click the load button in order to get it in a datagrid and finally post it to the database server. Regards,

Balaguru

[Christian Graus]

That's obvious. But, you're trying to read the file using the client side path, instead of writing it to the server.

Christian Graus Please read this if you don't understand the answer I've given you "also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )