Click here to Skip to main content
15,892,059 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm trying to generate InsertCommand to SqliteDataAdapter but got this error:

Unhandled Exception:

System.InvalidOperationException: Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information.


What I have tried:

using Mono.Data.Sqlite; 

public void Test(){
  var dataAdapter = new SqliteDataAdapter();
  var dataSet = new DataSet("TestTable");

  SqliteCommandBuilder commandBuilder = new SqliteCommandBuilder(dataAdapter);

  SqliteConnection connection = new SqliteConnection();
  connection.ConnectionString = "Data Source=" + dbPath;
  connection.Open();

  dataAdapter.SelectCommand = connection.CreateCommand();
  //dataAdapter.SelectCommand.CommandText = "SELECT * FROM TestTable";
  dataAdapter.SelectCommand.CommandText = "SELECT [ID], [Name] FROM TestTable";

  dataAdapter.InsertCommand = commandBuilder.GetInsertCommand(); // ERROR
}


Create table script:

CREATE TABLE `TestTable` (
    `ID`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `Name`  TEXT NOT NULL
);


Data in table

1  'Pedro'


Anyone can help?
Posted
Updated 6-Feb-19 1:06am
Comments
ZurdoDev 24-Jan-19 8:18am    
It means it is not supported. Break your sql up into different calls.
Desintrinski 24-Jan-19 8:50am    
Do you mean create manualy update, delete and insert command?
ZurdoDev 24-Jan-19 8:53am    
Yes, use a regular SqlCommand object to create the table first.
Desintrinski 24-Jan-19 9:04am    
I don't find any information that sqlite don't support what I'm trying to do. Can you please give my one source?
ZurdoDev 24-Jan-19 9:14am    
The error tells you it is not supported in DataAdapters.

1 solution

I'm not sure if it fits to SQLiteCommandBuilder, but MSDN states:
Quote:
The DbCommandBuilder must execute the SelectCommand in order to return the metadata necessary to construct the INSERT, UPDATE, and DELETE SQL commands. As a result, an extra trip to the data source is necessary, and this can hinder performance. To achieve optimal performance, specify your commands explicitly rather than using the DbCommandBuilder.

Maybe... SQLiteCommandBuilder must to sucessfully get data first (by using SelectCommand) to be able to create proper InsertCommand.

I'd recommend to follow the tip provided by ZurdoDev[^]:
Quote:
Yes, use a regular SqlCommand object to create the table first.


For further details, please see: Getting started with SQLite in C# – Tigran's Blog[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900