Solved

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

Posted on 2006-07-16
3
416 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 500 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

Suggested Solutions

Title # Comments Views Activity
VB6 Compile Compatibility Issue 4 116
"Black Box" Testing of Control System Software 2 72
printf performancy 11 73
Select specific duplicate row based on specific criteria 4 58
I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
This is about my first experience with programming Arduino.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

808 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