#oledbcommand

[innercreationthing-blog]

Update Oledb Access columns at run time in C#/.Net

Update Oledb Access columns at run time in C#/.Net

How to add fields to a access table in C# at run time ? This tutorial will share what you need to learn.

.Net Coder

First I have to say that , in this example used the Oledb adapter and connection which is not included in this code. You can check my previous posts on oledb connections(connection and Adapter).

I am going to put a checking section before adding column, others wise C# will…

[tsworman]

Opening Excel, CSV and Tabbed files from C# .Net

I set out to find a way to extract data from tab/csv/xls/xlsx files from C#.Net. If possible I’d use SQL to query them. 

The OLEDB providers make this possible. You’ll find lots of documentation online regarding the JET database engine, you can safely ignore it. Microsoft stopped producing JET and no 64 bit version is available. Instead you want to find and install a Microsoft ACE database provider.

ACE comes in 2 flavors, a 32 and a 64 bit version. Your ACE install processor architecture needs to match the processor architecture of your .Net project. If you are going to use this from withing IIS on a 64 bit machine, but want to run a 32 bit project, you need to make sure your application has 32 bit workers enabled in the application pool or it will not pick up the 32 bit version os the ACE driver. When it can’t find the right provider you’ll get the error: “The Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine “

Can I list the registered providers from .Net? I found no way to easily do that but it feels like something that should be easy to do. If you find a way, please let me know.

Note that Microsoft doesn’t seem to officially support using these for anything outside of desktop applications. 

Here’s how I did it: In my web.config add the following

 <connectionStrings>  <add name="Excel03ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'" />  <add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR={1}'" />  <add name="CSVConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Text;HDR={1};FMT=Delimited'" />  <add name="TABConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Text;HDR={1};FMT=TabDelimited'" />  </connectionStrings>

This are a centralized location to list your connection strings. the numbers enclosed in brackets are replaceable at runtime.

Here is the .Net Code to use them:

string conStr = "";            string filePath = "";            string fileExtension = Path.GetExtension(in_filePath);            switch (fileExtension.ToLower())            {                case ".csv": //CSV, if we want to support tab, we either change the tab to , or use a schema.ini file and list each file in it.                    filePath = Path.GetDirectoryName(in_filePath);                    conStr = ConfigurationManager.ConnectionStrings["CSVConString"].ConnectionString;                    break;                case ".xls" : //Excel 97-03                    filePath = in_filePath;                    conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;                    break;                case ".xlsx": //Excel 07                    filePath = in_filePath;                    conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;                    break;            }            DataTable dt = new DataTable();            conStr = String.Format(conStr, filePath, in_isHDR);            OleDbConnection connExcel = new OleDbConnection(conStr);            OleDbCommand cmdExcel = new OleDbCommand();            OleDbDataAdapter oda = new OleDbDataAdapter();            cmdExcel.Connection = connExcel;            //Get the name of First Sheet            connExcel.Open();            DataTable dtExcelSchema;            dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);            string SheetName = "";            switch (fileExtension.ToLower())            {                case ".csv": //CSV                    SheetName = Path.GetFileName(in_filePath);                    break;                case ".xls": //Excel 97-03                case ".xlsx": //Excel 07                    SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();                    break;            }            connExcel.Close();            //Read Data from First Sheet            connExcel.Open();            cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";            oda.SelectCommand = cmdExcel;            oda.Fill(dt);            connExcel.Close();

The end result is a data table filled with data from a tab/csv or first sheet of an excel file. 

[innercreationthing-blog]

Syntax Error INSERT INTO Statement - ADO.Net Solved

Syntax Error INSERT INTO Statement – ADO.Net Solved

This is a common error occurred during the database operation in ADO.Net. The cause for this error is the column name matches some reserved words. To solve this issue suffix and prefix with “] and [” in column names. Command object If you are using command object use the following method in the insert statement. OleDbCommand cmd = new OleDbCommand(“insert into acgroup(gname,parent,[note])…