Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2006-07-16
3
Medium Priority
?
591 Views
Last Modified: 2010-04-17
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
0
Comment
Question by:jhav1594
  • 2
3 Comments
 
LVL 18

Expert Comment

by:Ravi Singh
ID: 17117717
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
 

Author Comment

by:jhav1594
ID: 17117793
hello..thx a lot..is there a way of using a loop... instead of assigning values to each array string indivisually
0
 
LVL 18

Accepted Solution

by:
Ravi Singh earned 2000 total points
ID: 17117829
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
Progress
Loops Section Overview

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question