Friday, February 6, 2015

Dynamically create insert statement to insert records of DataTable into DataBase table


Useful when exporting the records from one Database(Oracle) to another Database(Oracle) table .


 using Oracle.DataAccess.Client;  

public static void Create_ORA_Entry(string connStr, DataTable dataTable, string tableName)  
     {  
       DataTable dt_dest = Db.ExecuteDataTable_ORACLE(connStr, "select * from tableName where [column_ID]=" + dataTable.Rows[0]["COLUMN_ID"].ToString(), "tableName");  
       if (dt_dest.Rows.Count > 0)  
       {  
         return; //record already exists! no action taken.   
       }  
       string columns = string.Join(","  
       , dataTable.Columns.Cast<DataColumn>().Where(c => dt_dest.Columns.Contains(c.ColumnName)).Select(c => c.ColumnName));  
       string values = string.Join(","  
       , dataTable.Columns.Cast<DataColumn>().Where(c => dt_dest.Columns.Contains(c.ColumnName)).Select(c => string.Format(":{0}", c.ColumnName)));  
       String sqlCommandInsert = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", tableName, columns, values);  
       using (var con = new OracleConnection(connStr))  
       using (var cmd = new OracleCommand(sqlCommandInsert, con))  
       {  
         con.Open();  
         foreach (DataRow row in dataTable.Rows)  
         {  
           cmd.Parameters.Clear();  
           foreach (DataColumn col in dataTable.Columns.Cast<DataColumn>().Where(c=>dt_dest.Columns.Contains(c.ColumnName)))  
           {  
             cmd.Parameters.Add(col.ColumnName, row[col]);  
           }  
           int inserted = cmd.ExecuteNonQuery();  
         }  
       }  
     }  


public static DataTable ExecuteDataTable_ORACLE(string dataConnectionString,  
               string sql, string tableName)  
     {  
       OracleDataAdapter oraAD = new OracleDataAdapter(sql, dataConnectionString);  
       DataTable dt = new DataTable(tableName);  
       try  
       {  
         oraAD.Fill(dt);  
       }  
       catch (Exception ex)  
       {  
         throw ex;  
       }  
       finally  
       {  
         oraAD.Dispose();  
       }  
       return dt;  
     }  




Wednesday, February 4, 2015

Office Update breaks ActiveX controls

It seems that a recent Office update has broken ActiveX controls on worksheets. The symptoms include (but are probably not limited to):
  1. the program used to create this object is forms. that program is not installed on your computer
  2. Being unable to use or change properties of any active controls on worksheets
  3. Error messages saying “Can’t insert object”
  4. Error 438 when trying to refer to an activex control as a member of a worksheet in code
To fix it, do this:


  1. Close all Office applications.
  2. Do a search in Windows Explorer – make sure to include hidden and system files and folders – for *.exd files (note: that’s not *.exe !!) and delete any you find Or run this script by doing copy to text file then rename .txt to .bat. 
  3.  DEL %appdata%\microsoft\forms\MSForms.exd  
     DEL %temp%\excel8.0\MSForms.exd  
     DEL %temp%\word8.0\MSForms.exd  
     DEL %temp%\PPT11.0\MSForms.exd  
     DEL %temp%\vbe\MSForms.exd  
     PAUSE  
  4. Restart your Office apps and test the controls again.

Please note that the .exd files will be recreated when you next use a workbook with an embedded active control – this is quite normal and should not cause you a problem!

Code Formater

Paste Here Your Source Code
Source Code Formatting Options
1) Convert Tab into Space :
2) Need Line Code Numbering :
3) Remove blank lines :
4) Embeded styles / Stylesheet :
5) Code Block Width :
6) Code Block Height :
7) Alternative Background :
Copy Formatted Source Code
 
Preview Of Formatted Code