C# inserting data from array of strings into MS Access Database

Hello...
can any one tell me how to insert data from an array of strings of an object into a MS Access Table.

i wud like to know how to insert each string in the array into a new column of the table

thanks
jhav1594Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Ravi SinghConnect With a Mentor Senior Software EngineerCommented:
Do you want to construct the SQL string dynamically? If you just mean add the parameters using a loop then you can replace the four statements that add a parameter with:

for (int i = 0; i < myStringArray.Length; i++)
{
     dbComm.Parameters.Add("@Val" + (i + 1), myStringArray[i]);
}
0
 
Ravi SinghSenior Software EngineerCommented:
Hi, I recommend reading up on ADO.NET in general before diving into database access in C#. However, you can modify and play around with the following example. Make sure to change the databasePath variable to point to your MS Access db and also the SQL INSERT INTO to refer to a valid table and also columns, the example will create a new record in the database with each column containing a string from the array:

using System.Data.OleDb;

...

      //Your array
      string[] myStringArray = new string[4];
      myStringArray[0] = "Test1";
      myStringArray[1] = "Test2";
      myStringArray[2] = "Test3";
      myStringArray[3] = "Test4";

      //Database stuff
      OleDbConnection dbConn = null;
      OleDbCommand dbComm = null;

      const string databasePath = @"C:\Temp\MyDB.mdb";

      try
      {
            dbConn = new OleDbConnection();
            dbConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + databasePath + ";User Id=admin;Password=;";
                  
            dbComm = new OleDbCommand();
            dbComm.Connection = dbConn;
            dbComm.CommandText = "INSERT INTO MyTable (Column1, Column2, Column3, Column4) " +
                              "VALUES (@Val1, @Val2, @Val3, @Val4)";
            dbComm.CommandType = CommandType.Text;
                        
            dbComm.Parameters.Add("@Val1", myStringArray[0]);
            dbComm.Parameters.Add("@Val2", myStringArray[1]);
            dbComm.Parameters.Add("@Val3", myStringArray[2]);
            dbComm.Parameters.Add("@Val4", myStringArray[3]);

            dbConn.Open();

            dbComm.ExecuteNonQuery();
      }
      catch (Exception ex)
      {
            //Handle database related exception
      }
      finally
      {
            dbConn.Close();
      }
0
 
jhav1594Author Commented:
hello..thx a lot..is there a way of using a loop... instead of assigning values to each array string indivisually
0
All Courses

From novice to tech pro — start learning today.