Solved

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
A short article about problems I had with the new location API and permissions in Marshmallow
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
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…

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now