Connect Access 2007/2010 Database Using C#

Friends,

In this post, we will see how can we connect Access 2007/2010 database with C#. As we know Microsoft introduced new Access format from Access 2007 onwards (.accdb extension), we cannot use the default JET.OLEDB provider that we used to use before.

With the new database, Microsoft also launched a new provider namely ACE.OLEDB to connect with the database. Let’s see a quick sample below –

        private DataTable GetStudents()
        {
            DataTable dt = new DataTable();
            using (OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Schools.accdb;Persist Security Info=False;"))
            {
                OleDbCommand cmd = new OleDbCommand("SELECT * FROM Students", con);
                con.Open();
                OleDbDataReader reader = cmd.ExecuteReader();

                if (reader.HasRows)
                    dt.Load(reader);

                con.Close();
            }
            return dt;
        }

The above code connects to an Access 2010 database using the new connection provider with Students table in Schools database and retrieve all records. The most important point to note in the code is the Connection Provider  – “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Schools.accdb;Persist Security Info=False;

Hope you like this post. Keep learning & sharing! Cheers!

How To INSERT Values Explicitly in Identity Column in SQL

Friends,

In this post, we will see how can we insert explicit values in an Identity column in a table in SQL Server. Normally, the data in IDENTITY column should not be entered manually, but there are certain circumstances where you want to insert custom data in the IDENTITY column.

If you try entering data in IDENTITY column, you get the following error –

Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table 'MyTbl' when IDENTITY_INSERT is set to OFF.

To resolve this error, you need to execute the following command on your SQL Server.

   SET IDENTITY_INSERT MyTbl ON
   GO

Executing the above command tells SQL to allow insertion in Identity columns of Table MyTbl. Be sure to change the table name in the query before executing on your SQL Server.

To Turn off insertion in IDENTITY columns, you can use the following command –

   SET IDENTITY_INSERT MyTbl OFF
   GO

Hope you like this post! Keep learning & sharing! Cheers!

Solution: ‘Microsoft.Jet.OLEDB.4.0′ provider is not registered on the local machine

Friends,

While working with Access, many times you may get the famous exception “Microsoft.Jet.OLEDB.4.0′ provider is not registered on the local machine“. This normally happens that your code runs fine on your development machine and when you try the run it on production server, this error comes. IN this post, we will see a solution to the same.

This happens because there is no 64 bit version of Jet. To resolve this, you need to do the following steps –

  • Select the Project in Solution Explorer and right-click on it to open its Properties
  • Select Build Tab from the Properties window, choose x86 as “Platform Target” on the screenjet-error
  • Save the project & recompile.

Hope this helps you! Keep learning & sharing! Cheers

Download Image Files From FTP Server in C#

Friends,

In my previous posts, I dealt with How To List Files From FTP Server, How To Delete File From FTP Server. In this post, we will see how can we can download an image file from FTP server using C# from within your Console or Windows Forms Application.

        private StreamReader DownloadFileFromFTP()
        {
             var request = (FtpWebRequest)WebRequest.Create("ftp://www.server.com/test.jpg");
             request.Method = WebRequestMethods.Ftp.DownloadFile;
             request.Credentials = new NetworkCredential("username", "password");
             request.UseBinary = true;
             
             using (FtpWebResponse response = (FtpWebResponse)request.GetResponse())
             {
                   using (Stream responseStream = response.GetResponseStream())
                   {
                        using (FileStream fs = new FileStream(destFileName, FileMode.Create))
                        {
                              byte[] buffer = new byte[102400];
                              int read = 0;
                              do
                              {
                                   read = responseStream.Read(buffer, 0, buffer.Length);
                                   fs.Write(buffer, 0, read);
                                   fs.Flush();
                              } while (!(read == 0));

                              fs.Flush();
                              fs.Close();
                        }
                   }
             }
        }

The above code downloads a file named “test.jpg” from the root location of the server “www.server.com”. If you see the code,we created an object of FtpWebRequest class and this time, we passed the RequestMethod as DownloadFile. This command tells the CLR to download the mentioned file from the server. Also, since we are dealing with images, we tell the server to transfer data in Binary format.

Hope you like this post! Keep learning and sharing! Cheers!

Solution : Oledb Exception – No value given for one or more required parameters

Friends,

In this post, we will see solution to an error that occurs when using OleDbConnection classes to connect with Access database. The exception says – “No value given for one or more required parameters”. This can occur while inserting, selecting, updating or deleting data in Access database.  The common problems to this error are as below –

  • The column name mentioned in the SELECT statement is incorrect – Lets say we have a column named “Image File Name” in Access and we write code as
    SELECT [Image FileName] FROM tblName

    In this case, when the command is executed, an OleDBException will be thrown. You need to ensure that the column names used in your SELECT query is correct.

  • The values passed for a column in query is incorrect – This is a case where you are trying to update a text column without passing the value in single quotes. For ex – we have a Text column named Class in table. To update this column, we are writing the belowquery-
    UPDATE tblName Set Class=Hello World

    If you notice we are updating the table by passing values in incorrect format. The correct format would be as below –

    UPDATE tblName Set Class='Hello World'

If you know about any other reason that can create this error, please share in comments or let me know and I will update the article. Hope this post helps you! Keep learning and sharing! Cheers!

Convert Text to Title Case As You Type in TextBox Control in Windows Forms

Friends,

In this post we will see how can we convert any text written by user in a TextBox Control to Title Case as they type in. If you don’t know about TitleCase, check it on Wikipedia here. In simple words, any sentence where 1st letter of every word is capital is known as to be present in Title Case. To implement this feature in your Windows Forms applications, perform the following steps –

  • Handle the TextChanged event handler of the TextBox
  • Write the below piece of code in the event handler.
         TextBox tb = sender as TextBox;
         tb.Text = Utility.ConvertToTitleCase(tb.Text);
         if (Char.IsUpper(tb.Text.ElementAtOrDefault(tb.Text.Length - 1)))
         {
              tb.SelectionStart = tbPatientComplaintDuration.Text.Length;
              tb.SelectionLength = 0;
         }
    
  • You’re done.

The complete event handler would look like below –

   private void OnTextChanged(object sender,EventArgs e)
   {
     TextBox tb = sender as TextBox;
     tb.Text = Utility.ConvertToTitleCase(tb.Text);
     if (Char.IsUpper(tb.Text.ElementAtOrDefault(tb.Text.Length - 1)))
     {
          tb.SelectionStart = tbPatientComplaintDuration.Text.Length;
          tb.SelectionLength = 0;
     }
   }

Hope this post helps you! Keep learning & sharing! Cheers!

Change Height of TextBox Control in Windows Forms

Friends,

From the title, this seems to be a very simple post that every novice programmer would know but changing the height of a textbox control in a Windows Forms Application is bit tricky that thought. You must be wondering that setting the Size.Height property of the control would do this, but as soon as you change this, it reverts back to the original default size. If not, go give a try and let me know if it works instantly. In this post, we will see ways on how to achieve this. There are few ways you can increase the height of the textbox. They are –

Option 1: Use MultiLine Property

You can set the MultiLine property of the TextBox control to True and then increase the height of the TextBox. This may not be desirable in some cases when you want to restrict users to enter only single line in the textbox.

textbox-2

Option 2: Change Designer.cs file

Open the Designer.cs file and scroll down to the place where your textbox properties are set. Add the following lines to set the height of the TextBox. However, if you are using this approach, beware that you changes get lost as soon as you make some change in the Form designer and hence this approach is highly recommended to be NOT used.

Option 3: Use MinimumSize Property

This is the most robust way I have found so far. To use this approach, you need to make 2 changes in your Textbox properties.

  1. Set MinimumSize Property of the TextBox to your desired size(Height & Width)
  2. Clear the Size Property of the TextBox to blank and hit Enter.

textbox

As you do this, your TextBox will resize to your desired Height & Width.

Hope you like this post! Keep learning and sharing ! Cheers!