Some Problem,Please help me
-
My application needs Exports to Excel and From Excel to Database for that ,i have used the code For Export: Dim mySQLCommand As New SqlCommand Dim myConnection As New SqlConnection Dim datagrid1 As New DataGrid Dim DS As New DataSet Dim DT As New DataSet myConnection = New SqlConnection("Password=technologies;Persist Security Info=False;User ID=sa;Initial Catalog=Lims_Demo;Data Source=VISHWA") 'ConfigurationSettings.AppSettings("MyConStr")) myConnection.Open() Dim da As New SqlDataAdapter("SELECT top 30 ProductCode,ProductName,GenericName,RetestPeriod FROM Results where status=0", myConnection) da.Fill(DT) datagrid1.DataSource = DT datagrid1.DataBind() Response.Clear() Response.ContentType = "application/vnd.ms-excel" Response.Charset = "" Me.EnableViewState = False Dim tw As New System.IO.StringWriter Dim hw As New Html32TextWriter(tw) datagrid1.RenderControl(hw) Response.Write(tw.ToString()) Dim path As String = "C:\...\abc.doc" Response.AddHeader("content-disposition", "attachment; filename=Worddocument.xls") Response.End() 'FileStream sourceFile = new FileStream(path, FileMode.Open); 'long FileSize; 'FileSize = sourceFile.Length; 'byte[] getContent = new byte[(int)FileSize]; 'sourceFile.Read(getContent, 0, (int)sourceFile.Length); 'sourceFile.Close(); 'Response.BinaryWrite(getContent); 'dim SaveFileDialog1 as System. 'If SaveFileDialog1.ShowDialog() = DialogResult.OK Then ' RichTextBox1.SaveFile(SaveFileDialog1.FileName, _ ' RichTextBoxStreamType.PlainText) 'End If myConnection.Close() the above coding is working very good and giving the required results but when i am going to Import Excel data to Database I have used the code, which i found in Msdn help '---------------------Importing Data From Excel to Database If Not FILE1.PostedFile.FileName = "" Then FilePath = FILE1.PostedFile.FileName End If 'Excel Work Sheet Database Connection MyConnection = New System.Data.OleDb.OleDbConnection( _ "provider=Microsoft.Jet.OLEDB.4.0; " & _ "data source=" & FilePath & "; " & _ "Extended Properties=Excel 8.0;") 'Select the data from Sheet1 of the workbook. 'MyConnection.Open() 'Dim dt As Table = MyConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null) Dim f As Array f = Split(FilePath, "\") Dim k As Integer = f.Length Dim str As String = f(k - 1) Dim filename As String = str.Substring(0, str.LastIndexOf(".")) 'str.Split(".xls") 'Dim f1 As Array 'f1 = Split(str, ".") 'Dim str1 As String = f1(f1.Length - 1) Try D
-
My application needs Exports to Excel and From Excel to Database for that ,i have used the code For Export: Dim mySQLCommand As New SqlCommand Dim myConnection As New SqlConnection Dim datagrid1 As New DataGrid Dim DS As New DataSet Dim DT As New DataSet myConnection = New SqlConnection("Password=technologies;Persist Security Info=False;User ID=sa;Initial Catalog=Lims_Demo;Data Source=VISHWA") 'ConfigurationSettings.AppSettings("MyConStr")) myConnection.Open() Dim da As New SqlDataAdapter("SELECT top 30 ProductCode,ProductName,GenericName,RetestPeriod FROM Results where status=0", myConnection) da.Fill(DT) datagrid1.DataSource = DT datagrid1.DataBind() Response.Clear() Response.ContentType = "application/vnd.ms-excel" Response.Charset = "" Me.EnableViewState = False Dim tw As New System.IO.StringWriter Dim hw As New Html32TextWriter(tw) datagrid1.RenderControl(hw) Response.Write(tw.ToString()) Dim path As String = "C:\...\abc.doc" Response.AddHeader("content-disposition", "attachment; filename=Worddocument.xls") Response.End() 'FileStream sourceFile = new FileStream(path, FileMode.Open); 'long FileSize; 'FileSize = sourceFile.Length; 'byte[] getContent = new byte[(int)FileSize]; 'sourceFile.Read(getContent, 0, (int)sourceFile.Length); 'sourceFile.Close(); 'Response.BinaryWrite(getContent); 'dim SaveFileDialog1 as System. 'If SaveFileDialog1.ShowDialog() = DialogResult.OK Then ' RichTextBox1.SaveFile(SaveFileDialog1.FileName, _ ' RichTextBoxStreamType.PlainText) 'End If myConnection.Close() the above coding is working very good and giving the required results but when i am going to Import Excel data to Database I have used the code, which i found in Msdn help '---------------------Importing Data From Excel to Database If Not FILE1.PostedFile.FileName = "" Then FilePath = FILE1.PostedFile.FileName End If 'Excel Work Sheet Database Connection MyConnection = New System.Data.OleDb.OleDbConnection( _ "provider=Microsoft.Jet.OLEDB.4.0; " & _ "data source=" & FilePath & "; " & _ "Extended Properties=Excel 8.0;") 'Select the data from Sheet1 of the workbook. 'MyConnection.Open() 'Dim dt As Table = MyConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null) Dim f As Array f = Split(FilePath, "\") Dim k As Integer = f.Length Dim str As String = f(k - 1) Dim filename As String = str.Substring(0, str.LastIndexOf(".")) 'str.Split(".xls") 'Dim f1 As Array 'f1 = Split(str, ".") 'Dim str1 As String = f1(f1.Length - 1) Try D
-
After you export the data to the excel file close it and then try to export the data from excel to database.
Best Regards, Apurva Kaushal
Thanks Apurv but Even Iam doing same thing what u said, it is giving same error kishore