Solved

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

Posted on 2006-07-16
3
444 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

732 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