none
Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information RRS feed

  • Question

  • Hello,


    I have the following table struture in SQLSERVER 2008:

    CREATE TABLE [dbo].[TableTestIdentityGuid](
    [M1SysKey] [int] IDENTITY(-2147483648,1) NOT NULL,
    [Pk] [uniqueidentifier] NOT NULL,
    [M1SysDeleted] [bit] NOT NULL,
    [M1SysRowVersion] [timestamp] NOT NULL,
    [Fk1] [uniqueidentifier] NOT NULL,
    [Fk2] [int] NOT NULL,
    CONSTRAINT [Pk_TableTestIdentityGuid] PRIMARY KEY NONCLUSTERED
    (
    [Pk] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [INDEXES]
    )ON [PRIMARY]

    However, when i try to generate update,delete and insert SQL statements on the tableadapter configuration wizard i'm always get the exception

    "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information".

    I tried to use

    SqlDataAdapter sda = new SqlDataAdapter("SELECT [Pk],[M1SysDeleted],[Pk_requisicao],[Pk_analise] FROM [M1_DEV].[dbo].[TableTestIdentityGuid]""ConnectionString");
    SqlCommandBuilder smb = new SqlCommandBuilder(sda);
    smb.GetUpdateCommand();

    But i had the same exception.

    Anyone can help me with this problem?

    Thank you

    Élsio Cardoso


    Tuesday, April 26, 2011 3:08 PM

Answers

  • I am not totally certain on this, but could it be because you are not including the IDENTITY column in your SELECT statement?  Try adding M1SysKey field to it:

     

    SqlDataAdapter sda = new SqlDataAdapter("SELECT [M1SysKey], [Pk],[M1SysDeleted],
    [Pk_requisicao],[Pk_analise] FROM [M1_DEV].[dbo].[TableTestIdentityGuid]", "ConnectionString");
    
    SqlCommandBuilder smb = new SqlCommandBuilder(sda);
    
    smb.GetUpdateCommand();
    
    

    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Tuesday, April 26, 2011 7:55 PM
  •  

    Hi Elsio,

    Welcome!

    You can refer here: http://support.microsoft.com/kb/308507

    This sample in the link is to create a sample Visual C# .NET console application that demonstrates how to use the SqlCommandBuilder object to automatically generate the DeleteCommand, the InsertCommand, and the UpdateCommand properties of the SqlCommand object for a SqlDataAdapter object.

    @jamesfreddyc is right!

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Saturday, April 30, 2011 2:29 AM
    Moderator

All replies

  • I am not totally certain on this, but could it be because you are not including the IDENTITY column in your SELECT statement?  Try adding M1SysKey field to it:

     

    SqlDataAdapter sda = new SqlDataAdapter("SELECT [M1SysKey], [Pk],[M1SysDeleted],
    [Pk_requisicao],[Pk_analise] FROM [M1_DEV].[dbo].[TableTestIdentityGuid]", "ConnectionString");
    
    SqlCommandBuilder smb = new SqlCommandBuilder(sda);
    
    smb.GetUpdateCommand();
    
    

    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Tuesday, April 26, 2011 7:55 PM
  •  

    Hi Elsio,

    Welcome!

    You can refer here: http://support.microsoft.com/kb/308507

    This sample in the link is to create a sample Visual C# .NET console application that demonstrates how to use the SqlCommandBuilder object to automatically generate the DeleteCommand, the InsertCommand, and the UpdateCommand properties of the SqlCommand object for a SqlDataAdapter object.

    @jamesfreddyc is right!

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Saturday, April 30, 2011 2:29 AM
    Moderator