Insert INTO error when transferring data from DataTable to Access Database

Question

I have a datatable with 5 columns, (Song, Artist, Album, Genre, Time) the table allows for me to enter as many rows as i want to create a playlist of music, when the user sees fit they can click the button export the data to access. My access database has a table named "Playlist" with the same 5 columns as the data table. When trying to transfer the data, i keep getting the exception error for the Insert INTO statement and I have no idea why because i am using a commandBuilder. I have attached my class and method thats performing this action.

Please advise!

public void ExportPlaylistToAccess(DataTable playlist)

{

 // open connection to the database pathed to

 String connection = @"Provider=Microsoft.ACE.OLEDB.12.0;" +

 @"Data source= D:\CIS 465\Final Project\VirtualiPod\iTunesPlaylistDatabase.accdb";

 using (OleDbConnection con = new OleDbConnection(connection))

{

 var adapter = new OleDbDataAdapter();

adapter.SelectCommand = new OleDbCommand("SELECT * from [Playlist]", con);

 var cbr = new OleDbCommandBuilder(adapter);

cbr.GetDeleteCommand();

cbr.GetInsertCommand();

cbr.GetUpdateCommand();

 try

{

con.Open();

adapter.Update(playlist);

}

 catch (OleDbException ex)

{

 MessageBox.Show(ex.Message, "Database Error");

}

 catch (Exception x)

{

 MessageBox.Show(x.Message, "Exception Error");

}

}

dataTable creation

private void createPlaylist_Click(object sender, EventArgs e)

{

 if (playlist.Rows.Count == 0)

{

playlist.Columns.Add("Song");

playlist.Columns.Add("Artist");

playlist.Columns.Add("Album");

playlist.Columns.Add("Genre");

playlist.Columns.Add("Time");

dataGridView1.DataSource = playlist;

}

 else if (playlist.Rows.Count > 0)

{

 MessageBox.Show("Please clear your current playlist to create a new one.");

}

}

 // adds song to playlist for user upon click

 private void addToPlaylist_Click(object sender, EventArgs e)

{

 IITTrackCollection tracks = app.LibraryPlaylist.Tracks;

 IITTrack currentTrack = app.CurrentTrack;

 DataRow newRow;

newRow = playlist.NewRow();

newRow["Song"] = currentTrack.Name;

newRow["Artist"] = currentTrack.Artist;

newRow["Album"] = currentTrack.Album;

newRow["Genre"] = currentTrack.Genre;

newRow["Time"] = currentTrack.Time;

playlist.Rows.Add(newRow);

dataGridView1.DataSource = playlist;

}

How did the datatable get created? If you just constructed one in code, the adapter wont know how to save the data in it to the DB, even though you used the same names. — Ňɏssa Pøngjǣrdenlarp, Apr 28 '16 at 01:05
I edited my original post where my dataTable is being created. — Imperiale, Apr 28 '16 at 01:08
The DataTable needs to be created from the destination table. Use your SELECT query for that. And make the DataAdapter a form/class level object so it retains the smarts for INSERT, DELETE and UPDATE. [example](http://stackoverflow.com/a/33702351/1070452) — Ňɏssa Pøngjǣrdenlarp, Apr 28 '16 at 01:12
...also, Time is a reserved word in Access SQL, hard to believe it is a string type too. — Ňɏssa Pøngjǣrdenlarp, Apr 28 '16 at 01:20
wow changing the column names to "duration" instead of "time" completely solved the problem. Thanks for your help! — Imperiale, Apr 28 '16 at 01:29

score 1 · Answer 1 · answered Aug 27 '16 at 18:12

Time is a reserved word. For some reason the command builder does not surround fields that are database reserved words (time, date, long etc.) with brackets [time] which would allow the insert query to work correctly. Without the brackets the insert will fail as the SQL compiler does not know if the string time is a sql command or a field name. The only solution I've found is to rename your database fields so that they are not in conflict with the database reserved names. Hopefully MS will eventually fix this mistake.

Insert INTO error when transferring data from DataTable to Access Database

1 Answers1