VB.NET - vb.net with excel upload - Asked By kiruba .e on 22-Jun-12 07:45 AM

Earn up to 20 extra points for answering this tough question.
Hi,

I got this error, while uploading excel sheet in access database.  First i get data from excel and assign it to datagridview.

Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + TxtFileName.Text + ";Extended Properties='Excel 4.0;HDR=YES;'"
        Dim xlsConn As OleDb.OleDbConnection = New OleDb.OleDbConnection(excelConnectionString)
        Dim command As OleDb.OleDbCommand = New OleDb.OleDbCommand("select * from [Sheet1$] ", xlsConn)
        Dim daexcel As OleDbDataAdapter
        daexcel = New OleDbDataAdapter
        daexcel.SelectCommand = command
        Dim dsexcel As New DataSet
        xlsConn.Open()
        daexcel.Fill(dsexcel) '----------------------------------------->In this line i got "The connection for viewing your linked Microsoft Excel worksheet was lost."
        Grdbirthday.DataSource = dsexcel.Tables(0)
        xlsConn.Close()

How to rectify it?

Regards
Kiruba.e
dipa ahuja replied to kiruba .e on 22-Jun-12 08:18 AM
Try this code : private void button2_Click(object sender, EventArgs e)
{
  string ExcelConstr = @"Provider=Microsoft.ACE.OLEDB.12.0";
  ExcelConstr += "Data Source=d:\book1.xls;Extended Properties=Excel 12.0";
 
  string SqlConstr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;";
  SqlConstr += "Integrated Security=True;User Instance=True";
 
  using (OleDbConnection con = new OleDbConnection(ExcelConstr))
  {
    con.Open();
    OleDbCommand com = new OleDbCommand("Select * from [Sheet1$]", con);
    OleDbDataReader dr = com.ExecuteReader();
    using (SqlConnection sqlcon = new SqlConnection(SqlConstr))
    {
      sqlcon.Open();
      using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon))
      {
        bulkCopy.DestinationTableName = "Table1";
        bulkCopy.ColumnMappings.Add("srno", "srno");
        bulkCopy.ColumnMappings.Add("amount", "amount");
        bulkCopy.WriteToServer(dr);
      }
    }
    dr.Close();
    dr.Dispose();
  }
  MessageBox.Show("successfully imported!");
  //display the imported data in the datagrid
  SqlDataAdapter da = new SqlDataAdapter("select * from Table1", SqlConstr);
  DataTable dt = new DataTable();
 
  da.Fill(dt);
 
  dataGridViewX1.DataSource = dt;
}
 
wally eye replied to kiruba .e on 22-Jun-12 10:29 AM
Have you verified that you have an active connection?  I assume TxtFileName.text has the right path in it, have you verified that?
[)ia6l0 iii replied to kiruba .e on 22-Jun-12 12:47 PM
First thing for you to do is to check if the file that you provided in "TxtFileName.Text" is Open. The file that is being read by the Oledb connection, should not be open. 

And remember to close all instances of Excel via the task manager, just to make sure that this is not an old cause. Please pass the excel file instance to this method to effectively dispose it. 

Private Sub NAR(o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
Catch
Finally
o = Nothing
End Try
End Sub


Hope this helps.
Jitendra Faye replied to kiruba .e on 25-Jun-12 02:22 AM

Using sqlBulk Class you can export excel file to DataBase.

Use this code-


protected void btnSend_Click(object sender, EventArgs e)
{
String strConnection = "Data Source=MySystem;Initial Catalog=MySamplesDB;Integrated Security=True";

//file upload path
string path = fileuploadExcel.PostedFile.FileName;

//Create connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";

//Create Connection to Excel work book
OleDbConnection excelConnection =new OleDbConnection(excelConnectionString);

//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]",excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "Excel_table";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
}

Try this code and let me know.