SQLite. Need help with updating a table

macnab

Member
Joined
Oct 26, 2014
Messages
12
Programming Experience
10+
I have a class Variables where I declare my tables. eg:
public static DataSet dsFoods = new DataSet();

I have a class Common where I have my data access routines.
At startup I use this to fill the datasets:
C#:
   sql = [COLOR=#0000ff]"SELECT * FROM "[/COLOR] + datasetName;
   SQLiteCommand selectSQL = [COLOR=#008b8b][B]new[/B][/COLOR] [COLOR=#191970][B]SQLiteCommand[/B][/COLOR](sql);
   selectSQL.Connection = Variables.conn;
   SQLiteDataAdapter da = [COLOR=#008b8b][B]new[/B][/COLOR] [COLOR=#191970][B]SQLiteDataAdapter[/B][/COLOR](selectSQL);
      
   [COLOR=#008080][B]try[/B][/COLOR]
   {
      da.[COLOR=#191970][B]Fill[/B][/COLOR](whichDataset);
   }
   [COLOR=#008080][B]catch[/B][/COLOR] (Exception ex)
   {
      errorString = [COLOR=#0000ff]"Error filling dataset "[/COLOR] + datasetName + [COLOR=#0000ff]"."[/COLOR] + Environment.NewLine +
         [COLOR=#0000ff]"Returned error is:"[/COLOR] + Environment.NewLine +
         ex.Message;
      MessageBox.[COLOR=#191970][B]Show[/B][/COLOR](errorString);
      [COLOR=#000080]return[/COLOR] [COLOR=#008b8b][B]false[/B][/COLOR];
   }
   [COLOR=#008080][B]finally[/B][/COLOR]
   {
      Variables.conn.[COLOR=#191970][B]Close[/B][/COLOR]();
   }
     
   [COLOR=#000080]return[/COLOR] [COLOR=#008b8b][B]true[/B][/COLOR];
Works fine. And I can add a row to the table.
But if I want to edit the table, I have problems saving the changes.
I end up with a dataset called changes which has the changes to dsFoods in it.

In Common, I have this:
C#:
   [COLOR=#0000ff][B]public[/B][/COLOR] [COLOR=#a52a2a]static[/COLOR] [COLOR=#ff0000]string[/COLOR] [COLOR=#191970][B]SaveDatasetChanges[/B][/COLOR](DataSet inds, [COLOR=#ff0000]string[/COLOR] tableName)
   {
      SQLiteCommand cmd = [COLOR=#008b8b][B]new[/B][/COLOR] [COLOR=#191970][B]SQLiteCommand[/B][/COLOR]([COLOR=#0000ff]""[/COLOR], Variables.conn);
      SQLiteDataAdapter da = [COLOR=#008b8b][B]new[/B][/COLOR] [COLOR=#191970][B]SQLiteDataAdapter[/B][/COLOR](cmd);
      SQLiteCommandBuilder cb = [COLOR=#008b8b][B]new[/B][/COLOR] [COLOR=#191970][B]SQLiteCommandBuilder[/B][/COLOR](da);

      [COLOR=#ff0000][B]int[/B][/COLOR] numRows = [COLOR=#00008b]0[/COLOR];
      [COLOR=#008080][B]try[/B][/COLOR]
      {
         Variables.conn.[COLOR=#191970][B]Open[/B][/COLOR]();
         numRows = da.[COLOR=#191970][B]Update[/B][/COLOR](inds, tableName);
      }
      [COLOR=#008080][B]catch[/B][/COLOR] (Exception ex)
      {
         [COLOR=#000080]return[/COLOR] ex.Message;
      }
      [COLOR=#000080]return[/COLOR] numRows.[COLOR=#191970][B]ToString[/B][/COLOR]();
   }
I call it, never minding the rest,:
resultStr = Common.SaveDatasetChanges(inds, ""Foods");
where inds is the dataset containing the changes.
I get an error:
Update unable to find TableMapping['Foods'] or DataTable 'Foods'

I have spent hours fiddling but I am getting nowhere.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,124
Location
Sydney, Australia
Programming Experience
10+
The error message seems pretty clear: there is no DataTable in the Tables collection of the DataSet with a TableName of "Foods". Perhaps you're under the misconception that a DataTable will automatically take its name from the database table that you query. That is not the case. If you want a DataTable to have a name then you have to give it one, either setting the TableName yourself or, more logically, by providing a name when you call Fill on your data adapter, just as you're doing when you call Update.
 

macnab

Member
Joined
Oct 26, 2014
Messages
12
Programming Experience
10+
SQLiteManager says the the table's name is Foods.

This is from my main form:
C#:
       [COLOR=#0000ff][B]if[/B][/COLOR] (!Common.[COLOR=#191970][B]FillDataset[/B][/COLOR](Variables.dsFoods, [COLOR=#0000ff]"Foods"[/COLOR])) [COLOR=#000080]return[/COLOR];
That works, so the table Foods must exist.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,124
Location
Sydney, Australia
Programming Experience
10+
SQLiteManager says the the table's name is Foods.

This is from my main form:
C#:
       [COLOR=#0000ff][B]if[/B][/COLOR] (!Common.[COLOR=#191970][B]FillDataset[/B][/COLOR](Variables.dsFoods, [COLOR=#0000ff]"Foods"[/COLOR])) [COLOR=#000080]return[/COLOR];
That works, so the table Foods must exist.
Perhaps you should read what I posted a bit more carefully.
The error message seems pretty clear: there is no DataTable in the Tables collection of the DataSet with a TableName of "Foods".
I specifically pointed out that the name of the database table is irrelevant.
Perhaps you're under the misconception that a DataTable will automatically take its name from the database table that you query. That is not the case.
I even told you what to do about it.
If you want a DataTable to have a name then you have to give it one, either setting the TableName yourself or, more logically, by providing a name when you call Fill on your data adapter, just as you're doing when you call Update.
 

macnab

Member
Joined
Oct 26, 2014
Messages
12
Programming Experience
10+
Just checked, my datasets all have TableName "Table", so they are not set when I call fill. Hence the use of Table[0] throughout, as opposed to Table["Foods"]
Fixed that bit manually:
C#:
   whichDataset.Tables[[COLOR=#00008b]0[/COLOR]].TableName = datasetName;
However, I presume there is a more elegant way. And I found it - TableMappings.Add

So now it works.
And I learned something important.
Thanks.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,124
Location
Sydney, Australia
Programming Experience
10+
Just checked, my datasets all have TableName "Table", so they are not set when I call fill. Hence the use of Table[0] throughout, as opposed to Table["Foods"]
Fixed that bit manually:
C#:
   whichDataset.Tables[[COLOR=#00008b]0[/COLOR]].TableName = datasetName;
However, I presume there is a more elegant way. And I found it - TableMappings.Add

So now it works.
And I learned something important.
Thanks.
Or you could do what I said 7 hours ago and then repeated 3.5 hours ago:
If you want a DataTable to have a name then you have to give it one ... by providing a name when you call Fill on your data adapter, just as you're doing when you call Update.
 

macnab

Member
Joined
Oct 26, 2014
Messages
12
Programming Experience
10+
I got it:
C#:
    da.[COLOR=#191970][B]Fill[/B][/COLOR](whichDataset, datasetName);
I really need to find a good resource to read through.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,124
Location
Sydney, Australia
Programming Experience
10+
I really need to find a good resource to read through.
There are lots of tutorials around but they won't all provide all the information you want and may well do the same thing in different ways. Once you know the types and members you're interested in though, it's always a good idea to visit the MSDN Library, accessible via the VS Help menu, and read the corresponding documentation. That's an excellent way to see what overloads a method has and what members a type has.
 

macnab

Member
Joined
Oct 26, 2014
Messages
12
Programming Experience
10+
On reflection you are right. Googling questions I have never seen one example where the table-name is set in the data adapter.
 
Top Bottom