Mehmet Yilmaz

Mehmet Yilmaz

  • NA
  • 29
  • 2.2k

Excell to Acces Data Transfer

Aug 23 2017 8:16 AM
I just want to get the lines with tax ID "78898989898" and I want the numbers to be transferred in "0.00" format
 
  1. string patchmdb;  
  2.         OleDbConnection cnEMP = new OleDbConnection();  
  3.   
  4.         private void button1_Click(object sender, EventArgs e)  
  5.         {  
  6.   
  7.             string patchxls = Application.StartupPath + "\\taxlist.xls";  
  8.   
  9.             DataTable dtEMP = new DataTable();  
  10.             string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + patchxls + "; Extended Properties='Excel 8.0;HDR=No;'";  
  11.             using (OleDbConnection cnEMP = new OleDbConnection(ConnectionString.ToString()))  
  12.             {  
  13.                 cnEMP.Open();  
  14.                 using (OleDbCommand cmdEMP = new OleDbCommand("SELECT * FROM [tax$C5:M]", cnEMP))  
  15.                 {  
  16.                     using (OleDbDataReader drEMP = cmdEMP.ExecuteReader())  
  17.                     {  
  18.                         dtEMP.Load(drEMP);  
  19.                         dataGridView1.DataSource = dtEMP;  
  20.                     }  
  21.                 }  
  22.                 cnEMP.Close();  
  23.             }  
  24.   
  25.             foreach (DataGridViewRow EMPRow in dataGridView1.Rows)  
  26.             {  
  27.                 patchmdb = Application.StartupPath + "\\data.mdb";  
  28.   
  29.                 cnEMP.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + patchmdb;  
  30.                 cnEMP.Open();  
  31.                 OleDbCommand cmdEMP = new OleDbCommand();  
  32.                 cmdEMP.Connection = cnEMP;  
  33.   
  34.   
  35.                 string qryEMP = "INSERT INTO bill([a], [b], [c], [d], [e], [f], [g], [h], [j], [k]) ";  
  36.                 qryEMP = qryEMP + "Values('" + EMPRow.Cells[0].Value + "','" + EMPRow.Cells[2].Value + "','" + EMPRow.Cells[7].Value + "','" + EMPRow.Cells[8].Value + "','" + null + "','" + null + "','" + null + "','" + null + "','" + null + "','" + null + "')";  
  37.                 cmdEMP.CommandText = qryEMP;  
  38.                 cmdEMP.CommandType = CommandType.Text;  
  39.                 cmdEMP.ExecuteNonQuery();  
  40.                 cnEMP.Close();  
  41.             }  
  42.   
  43.             OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + patchmdb);  
  44.             connection.Open();  
  45.   
  46.             OleDbCommand kdvsil = new OleDbCommand("DELETE FROM bill WHERE Len(Trim(a &''))=0;", connection);  
  47.             kdvsil.ExecuteNonQuery();  
  48.             connection.Close();        
  49.         }  
 https://1drv.ms/u/s!Al0E9WhLjd-q9CuIQE7TjKHA1_HZ
 

Attachment: excelltomdb.rar

Answers (2)