Re: System.InvalidOperationException when attempting to add a record

Hi,

When you call Update method of the DataAdapter, then it tries to build
INSERT, UPDATE and DELETE statements based on SELECT statement that selected
data. But it does not work with all the providers and in all the situations.
I believe you need to provide these SQL statements explicitly to resolve
this issue

--
Val Mazur
Microsoft MVP

http://xport.mvps.org



"JMB" <JMB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E17A09F5-34D6-4419-B2D6-FA03A1A8AD44@xxxxxxxxxxxxxxxx
>I am working on a VB.NET application that needs to add records to a FoxPro
> table. I create a dataset, populate a DataRow and add it to the dataset.
> Standard stuff I've done many times with SQL Server and Access databases.
> When I do the Update method on the dataset I get the following error:
> System.InvalidOperationException: Dynamic SQL generation is not supported
> against a SelectCommand that does not return any base table information.
>
> I'm guessing it's because the base table does not have a primary key. The
> table does have an index (CDX file). In FoxPro, I'd do a SET ORDER TO...
> command to specify the index to use. If that is the problem, how would I
> do
> that in VB.NET?
>
> This table has 119 columns in it, many of which are text and can contain
> any
> user input values. As a result, I don't want to attempt to do a SQL Insert
> Into command.
>
> Any suggestions? Thanks in advance.
>


.

Relevant Pages

  • Re: Post Revised: A Desperate Plea for Help
    ... The original poster already stated that he had Inser, Update and Delete SQL: ... That's why my advice was to call the data adapter's Update method. ... But the bottom line here is that you need to create the SQL Statements for these command yourself and so you need to know SQL. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Post Revised: A Desperate Plea for Help
    ... The original poster already stated that he had Inser, Update and Delete SQL: ... "Now, the adapters, I have noticed, contain command text for INSERT, UPDATE, ... Update method will in turn call these sql statements: ... That's why my advice was to call the data adapter's Update method. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: System.InvalidOperationException when attempting to add a reco
    ... indicated that I can't update FoxPro tables like I would like to using the ... >> Standard stuff I've done many times with SQL Server and Access databases. ... >> against a SelectCommand that does not return any base table information. ... >> command to specify the index to use. ...
    (microsoft.public.data.ado)
  • Re: why and when to use sqlCommandBuilder
    ... You should try and avoid using SqlCommandBuilder as a matter of fact. ... It cannot generate sql for every single possible case. ... > and delete command of the DataAdapter. ... > with the Update method of the data adapter to write the changes back ...
    (microsoft.public.dotnet.framework.adonet)
  • dataset saving to sql ce
    ... create table command is as follows: ... Is the code using the Update method ... allowed as a primary key in SQL CE ... comma with ')' ...
    (microsoft.public.dotnet.framework.compactframework)