CODEWITHSUNDEEP
c#ms-access
SaraniO
SaraniO

Reputation: 607

How to insert a datatable to an access database in C#

I have 2 table in an access database

now I want to select from one table and insert them into another one.

this is my code but it shows an exception in line Cmd.ExecuteNonQuery();

{"Syntax error (missing operator) in query expression 'System.Object[]'."}

the code is : 

public static void SetSelectedFeedIntoDB(Form2 frm2)
{
    string StrCon = System.Configuration.ConfigurationManager.ConnectionStrings["FeedLibraryConnectionString"].ConnectionString;
    OleDbConnection Connection = new OleDbConnection(StrCon);
    OleDbDataAdapter DataA = new OleDbDataAdapter("Select * from FeedLibrary where ID=" + frm2.FeedSelectListBox.SelectedValue, Connection);
    DataTable DTable = new DataTable();
    DataA.Fill(DTable);

    OleDbCommand Cmd = new OleDbCommand();
    Cmd.Connection = Connection;

    Connection.Open();

    foreach (DataRow DR in DTable.Rows)
    {
        Cmd.CommandText = "insert into SelectedFeeds Values(" + DR.ItemArray + ")";
        Cmd.ExecuteNonQuery();
    }

    Connection.Close();
}

what should I do to fix this?

Upvotes: 3

Views: 8141

Answers (1)

Steve
Steve

Reputation: 216363

Your error is caused by the fact that you are concatenating the ItemArray property of a DataRow to a string. In this case the ItemArray (that is an instance of an object[]) has no method that automatically produces a string from its values and thus returns the class name as a string "object[]" but of course this produces the meaningless sql string 

"insert into SelectedFeeds Values(object[])";

But you could simply build a SELECT .... INTO statement that will do everything for you without using DataTables and Adapters

string cmdText = @"SELECT FeedLibrary.* INTO [SelectedFeeds] 
                   FROM FeedLibrary
                   where ID=@id";
using(OleDbConnection Connection = new OleDbConnection(StrCon))
using(OleDbCommand cmd = new OleDbCommand(cmdText, Connection))    
{
     Connection.Open();
     cmd.Parameters.Add("@id", OleDbType.Integer).Value = Convert.ToInt32( frm2.FeedSelectListBox.SelectedValue);
     cmd.ExecuteNonQuery();
}

However, the SELECT ... INTO statement creates the target table but gives error if the target table already exists. To solve this problem we need to discover if the target exists. If it doesn't exist we use the first SELECT ... INTO query, otherwise we use a INSERT INTO ..... SELECT 

 // First query, this creates the target SelectedFeeds but fail if it exists
 string createText = @"SELECT FeedLibrary.* INTO [SelectedFeeds] 
                       FROM FeedLibrary
                       where ID=@id";
 // Second query, it appends to SelectedFeeds but it should exists
 string appendText = @"INSERT INTO SelectedFeeds
                       SELECT * FROM FeedLibrary
                       WHERE FeedLibrary.ID=@id";

using(OleDbConnection Connection = new OleDbConnection(StrCon))
using(OleDbCommand cmd = new OleDbCommand("", Connection))    
{
     Connection.Open();

     // Get info about the SelectedFeeds table....
     var schema = Connection.GetSchema("Tables", 
                   new string[] { null, null, "SelectedFeeds", null});

     // Choose which command to execute....
     cmd.CommandText = schema.Rows.Count > 0 ? appendText : createText;

     // Parameter @id is the same for both queries
     cmd.Parameters.Add("@id", OleDbType.Integer).Value = Convert.ToInt32( frm2.FeedSelectListBox.SelectedValue);

     cmd.ExecuteNonQuery();
}

Here we have two different queries, the first one create the SelectedFeeds table as before, the second one appends into that table.
To discover if the target table has already been created I call Connection.GetSchema to retrieve a datatable (schema) where there is a row if the table SelectedFeeds exists or no row if there is no such table.
At this point I set the OleDbCommand with the correct statement to execute.

Upvotes: 6

Related Questions