none
Insert into table variable in SP causes typed DS to fail RRS feed

  • Question

  • Hello
    When using "INSERT INTO @SomeTable..." (a table variable) from a stored procedure, my typed dataset doesn't return anything. However, when getting data manually via an SqlConnection, SqlCommand and (untyped)DataAdapter - it works fine.
    I've created a simple example demonstrating the issue. I have the following SP:
    GO
    CREATE PROCEDURE SomeProc
    AS
    BEGIN
    	DECLARE @TmpTbl table (id int);
    	
    	INSERT INTO @TmpTbl (id) VALUES (1);
    	
    	SELECT * FROM @TmpTbl;
    END
    

     Then I'm adding a typed dataset to my VS2010 C# project, defined in the following way:
    DataTable - SomeProc with one field: id
    TableAdapter - SomeProcTableAdapter with methods: Fill(), GetData()

    And the application:
    static void Main(string[] args)
    {
      // Get data via typed dataset
    
      Console.WriteLine("Typed dataset:");
    
      SomeDSTableAdapters.SomeProcTableAdapter adapter_1 = new SomeDSTableAdapters.SomeProcTableAdapter();
      SomeDS.SomeProcDataTable dt_1 = adapter_1.GetData();
    
      if (dt_1.Rows.Count == 0)
        Console.WriteLine("No data received");
      else
        foreach (SomeDS.SomeProcRow row in dt_1.Rows)
          Console.WriteLine("id " + row.id);
    
      // Get data "manually"
    
      Console.WriteLine("\nManually:");
    
      using (SqlConnection conn = new SqlConnection(@"Data Source=localhost\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=tmpdebug"))
      {
        conn.Open();
    
        SqlCommand cmd = new SqlCommand("SomeProc", conn);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
    
        SqlDataAdapter adapter_2 = new SqlDataAdapter(cmd);
        DataTable dt_2 = new DataTable();
        adapter_2.Fill(dt_2);
            
        if (dt_2.Rows.Count == 0)
          Console.WriteLine("No data received");
        else
          foreach (DataRow row in dt_2.Rows)
            Console.WriteLine("id " + row[0]);
      }
    }
    

    The result is:
    Typed dataset:
    No data received
    
    Manually:
    id 1

    It seems to be a problem related to the typed dataset, since the SP works fine when invoked directly from SQL Management Studio Express etc. I've tried both on SQL Server 2000 and 2008 Express with same outcome.
    If I change the SP to:
    	DECLARE @TmpTbl table (id int);
    	
    	--INSERT INTO @TmpTbl (id) VALUES (1);
    	
    	--SELECT * FROM @TmpTbl;
    	
    	SELECT 1 AS id;
    

    ... both the typed dataset and manual connection works - returning:
    Typed dataset:
    id 1
    
    Manually:
    id 1

    It's only when INSERT INTO... is invoked that no data is received by the typed dataset.
    It would be nice to know why this is happening. I'm not forced to use a typed dataset, but it's just bugging me :P
    Monday, July 4, 2011 7:39 AM

Answers

  • Well... the problem seems to be that the typed dataset is using an OleDbConnection instead of SqlConnection. My own code fails if I use OleDbConnection (meaning no data is received).

    Is there an easy way to tell the typed DS that it should use SqlConnection instead?

    Sorry ... pet peeve of mine. It's not the Typed DataSet that cares about the connection. It's the TableAdapter. It's unfortunate that the TableAdapter gets generated in the Typed DataSet Designer.cs file. This behavior has been irritating me for years!   But, my rant doesn't help you solve your problem, now does it?  ;0)

    I recommend not using the TableAdapters, and I have a blog post that tells how to create the Typed DataSet without all the TableAdapter baggage:

    http://geek-goddess-bonnie.blogspot.com/2010/04/create-xsd.html

    The above link also contains a link to my rant against TableAdapters. You get two posts for the price of one. ;0) I can also point you to some of my DataAccess class blog posts if you're interested.


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by ShaniaTwain Thursday, July 7, 2011 7:48 AM
    Thursday, July 7, 2011 5:02 AM

All replies

  • Well... the problem seems to be that the typed dataset is using an OleDbConnection instead of SqlConnection. My own code fails if I use OleDbConnection (meaning no data is received).

    Is there an easy way to tell the typed DS that it should use SqlConnection instead?

    Monday, July 4, 2011 10:45 AM
  • Hi Vort280,

    If you have any time, could you please try to add this

    this.getPropertyTableAdapter.Connection.ConnectionString = SQL_CONNECTION_STRING;

    when loading your project.

    If you have any updates, please feel free to let me know.

    

    Good day,


    Jackie Sun [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.

    Tuesday, July 5, 2011 9:39 AM
    Moderator
  • Hi Jackie

    Sorry, I'm not sure I understand perfectly...
    When loading my project? Do you mean manually specifying the connectionstring on the typed dataadapter like so:

    adapter_1.Connection.ConnectionString = @"Provider=SQLOLEDB;Data Source=localhost\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=tmpdebug";
    

    I guess that's not what you meant? The result is the same (no data from typed ds using the OleDBConn). 

    Tuesday, July 5, 2011 12:53 PM
  • Well... the problem seems to be that the typed dataset is using an OleDbConnection instead of SqlConnection. My own code fails if I use OleDbConnection (meaning no data is received).

    Is there an easy way to tell the typed DS that it should use SqlConnection instead?

    Sorry ... pet peeve of mine. It's not the Typed DataSet that cares about the connection. It's the TableAdapter. It's unfortunate that the TableAdapter gets generated in the Typed DataSet Designer.cs file. This behavior has been irritating me for years!   But, my rant doesn't help you solve your problem, now does it?  ;0)

    I recommend not using the TableAdapters, and I have a blog post that tells how to create the Typed DataSet without all the TableAdapter baggage:

    http://geek-goddess-bonnie.blogspot.com/2010/04/create-xsd.html

    The above link also contains a link to my rant against TableAdapters. You get two posts for the price of one. ;0) I can also point you to some of my DataAccess class blog posts if you're interested.


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by ShaniaTwain Thursday, July 7, 2011 7:48 AM
    Thursday, July 7, 2011 5:02 AM
  • Thanks, I'll look into that.
    Thursday, July 7, 2011 7:48 AM
  • Let us know if it helps to move away from the TableAdapters.

    Also, in case you *are* interested in the DataAccess class posts I mentioned, what the heck ...  here they are:

    http://geek-goddess-bonnie.blogspot.com/2009/09/dataaccess-part-i.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-iii.html

    Each post adds extra complexity to the Data Access classes, but more flexiblity. The first post is enough to get you going in the right direction and give you a general idea of the concept, but the second post is more useful. The third post gets into using anonymous delegates.


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Thursday, July 7, 2011 2:49 PM