none
Syntax for using CURSOR with ADO.NET RRS feed

  • Question

  • I have some code that uses ADO.NET like:

            // Get rid of the out of band data (non-ascii stuff)
            command.CommandText = "DECLARE @MyCursor FOR SELECT [AmazonCategory],[BuyCategory] FROM [#DefinitionTable] " +
                       "DECLARE @AmazonCategory nvarchar(128), @BuyCategory nvarchar(128) " +
                       "OPEN @MyCursor " +
                       "FETCH NEXT FROM @MyCursor INTO @AmazonCategory, @BuyCategory " +
                       "WHILE @@FETCH_STATUS = 0 " +
                       "BEGIN " +
                       "  UPDATE [#DefinitionTable] " +
                       "  SET AmazonCategory = @AmazonCategory, BuyCategory = @BuyCategory " +
                       "  WHERE CURRENT OF @MyCursor " +
                       "  FETCH NEXT FROM @MyCursor INTO @AmazonCategory, @BuyCategory " +
                       "END " +
                       "CLOSE @MyCursor " +
                       "DEALLOCATE @MyCursor " +
                       "GO";
            command.CommandType = CommandType.Text;
            rowsAffected = command.ExecuteNonQuery();
    
    

    But when I run this code I get an exception from SQL indicating that I must declare @MyCursor. As can be seen I do declare the CURSOR. There must be either a scoping issue or some syntax error.

    The stack trace for the exception looks like:

    System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'FOR'.

     

    Must declare the scalar variable "@MyCursor".
    Must declare the scalar variable "@MyCursor".
    Must declare the scalar variable "@MyCursor".
    Must declare the scalar variable "@MyCursor".
    Must declare the scalar variable "@MyCursor".
    Must declare the scalar variable "@MyCursor".
      at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
      at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
      at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
      at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
      at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
      at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
      at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    

     

    Any ideas?

    Kevin

     


    Kevin Burton
    Wednesday, December 1, 2010 7:42 PM

Answers

  • The update is not shown. Assume that the AmazonCategory gets changed using some T-SQL string functions. Now I want to persist the changes back to the temporary table "#DefinitionTable"? What would be the syntax for that?

    Thanks again.

    Kevin


    Kevin Burton


    Kevin,

    I don't really have enough info from the code you've posted so far, but you'd just need to join the @table to the #temp and do a normal UPDATE statement.  But this is outside of what I had posted earlier and you'll need to add another ID row from the #temp when inserting into the @Table variable.  Plus you'll need to add your data processing code in the T-SQL as well...

    Anyway, perhaps something like this:

    
    DECLARE @TTab Table (DefTab_ID int, AmazonCategory nvarchar(128), BuyCategory nvarchar(128))
    
    INSERT @TTab(DefTab_ID, AmazonCategory, BuyCategory)
    SELECT [DefTabID], [AmazonCategory],[BuyCategory] FROM [#DefinitionTable]
    
    
    --- process the @TTab Table Variable and then update the 
    --- #temp table with it
    
    
    UPDATE #DefinitionTable
    SET #DefinitionTable.AmzazonCategory = tt.AmazonCategory,
       #DefinitionTable.BuyCategory = tt.BuyCategory
    FROM #DefinitionTable dt
    INNER JOIN @TTab tt ON dt.DefTabID = tt.DefTab_ID
    
    
    
    
    
    

    Also, you might want to check in over at the TransactSQL forums too.  Lots of people that are sharper than me at writing T-SQL.


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    • Marked as answer by KevinBurton Thursday, December 2, 2010 2:49 AM
    Thursday, December 2, 2010 1:01 AM
  • Server-side cursors are not supported directly by ADO.NET, but you can use them. The below example looks like one method you may want to try:

    http://www.codeproject.com/KB/database/SimulatingRecordsets.aspx

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by KevinBurton Thursday, December 2, 2010 3:26 PM
    Thursday, December 2, 2010 3:13 PM

All replies

  • Kevin,

    I know this isn't a direct answer and forgive me for the suggestion, but why are you wanting to use CURSOR?  Wouldn't you be better off using @Table Variables or even a #Temp Table?

     


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Wednesday, December 1, 2010 7:52 PM
  • I need to update possibly every row in the table. I know how to use CURSORs better than I know how to use table variables. If you know how I could use table variables in this situation I would love to learn something new.

    As  you can see from the text I am updating a temporary table so I am already using a temporary table.

    Thank you.

    Kevin


    Kevin Burton
    Wednesday, December 1, 2010 7:59 PM
  • It would look something like this in a new query window in SQL Server:

     

    DECLARE @TTab Table (AmazonCategory nvarchar(128), BuyCategory nvarchar(128))
    
    INSERT @TTab( AmazonCategory, BuyCategory)
    SELECT [AmazonCategory],[BuyCategory] FROM [#DefinitionTable]
    

    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Wednesday, December 1, 2010 8:19 PM
  • I guess the Command would look something like this:

     

    Dim cmd As SqlCommand = New SqlCommand
    cmd.CommandType = CommandType.Text
    cmd.CommandText = "DECLARE @TTab Table (AmazonCategory nvarchar(128), BuyCategory nvarchar(128))" & vbNewLine _ 
    & "INSERT @TTab( AmazonCategory, BuyCategory)" & vbNewLine _
    & "SELECT [AmazonCategory],[BuyCategory] FROM [#DefinitionTable]"
    
    Using cmd
     cmd.Connection = Me.sqlCn
     cmd.ExecuteNonQuery()
    End Using
    
    

    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Wednesday, December 1, 2010 8:26 PM
  • The update is not shown. Assume that the AmazonCategory gets changed using some T-SQL string functions. Now I want to persist the changes back to the temporary table "#DefinitionTable"? What would be the syntax for that?

    Thanks again.

    Kevin


    Kevin Burton
    Wednesday, December 1, 2010 10:01 PM
  • The update is not shown. Assume that the AmazonCategory gets changed using some T-SQL string functions. Now I want to persist the changes back to the temporary table "#DefinitionTable"? What would be the syntax for that?

    Thanks again.

    Kevin


    Kevin Burton


    Kevin,

    I don't really have enough info from the code you've posted so far, but you'd just need to join the @table to the #temp and do a normal UPDATE statement.  But this is outside of what I had posted earlier and you'll need to add another ID row from the #temp when inserting into the @Table variable.  Plus you'll need to add your data processing code in the T-SQL as well...

    Anyway, perhaps something like this:

    
    DECLARE @TTab Table (DefTab_ID int, AmazonCategory nvarchar(128), BuyCategory nvarchar(128))
    
    INSERT @TTab(DefTab_ID, AmazonCategory, BuyCategory)
    SELECT [DefTabID], [AmazonCategory],[BuyCategory] FROM [#DefinitionTable]
    
    
    --- process the @TTab Table Variable and then update the 
    --- #temp table with it
    
    
    UPDATE #DefinitionTable
    SET #DefinitionTable.AmzazonCategory = tt.AmazonCategory,
       #DefinitionTable.BuyCategory = tt.BuyCategory
    FROM #DefinitionTable dt
    INNER JOIN @TTab tt ON dt.DefTabID = tt.DefTab_ID
    
    
    
    
    
    

    Also, you might want to check in over at the TransactSQL forums too.  Lots of people that are sharper than me at writing T-SQL.


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    • Marked as answer by KevinBurton Thursday, December 2, 2010 2:49 AM
    Thursday, December 2, 2010 1:01 AM
  • I guess there is no answer to the original question of the syntax for the CURSOR solution using ADO.NET?

    Kevin


    Kevin Burton
    Thursday, December 2, 2010 11:49 AM
  • I guess there is no answer to the original question of the syntax for the CURSOR solution using ADO.NET?

    Kevin


    Kevin Burton


    EDIT: Kevin -- I don't think it's going to work.  Everything I've read regarding CURSOR as Output is not available as an ADO.NET Command Parameter.  I still think you should use something other than CURSOR for your processing requirements.  There are far more efficient and appropriate set-based methods available to you that will return that result set back to your ADO.NET application.

    Not sure if you can output a CURSOR.

    DECLARE @MyCursor OUTPUT
    
    SET @MyCursor = CURSOR FOR SELECT [AmazonCategory], [BuyCategory] FROM [#DefinitionTable]
    
    
    

     command.CommandText = "DECLARE @MyCursor OUTPUT " +
    "SET @MyCursor = CURSOR FOR SELECT [AmazonCategory],[BuyCategory] FROM [#DefinitionTable] " +
         "DECLARE @AmazonCategory nvarchar(128), @BuyCategory nvarchar(128) " +
         "OPEN @MyCursor " +
         "FETCH NEXT FROM @MyCursor INTO @AmazonCategory, @BuyCategory " +
         "WHILE @@FETCH_STATUS = 0 " +
         "BEGIN " +
         " UPDATE [#DefinitionTable] " +
         " SET AmazonCategory = @AmazonCategory, BuyCategory = @BuyCategory " +
         " WHERE CURRENT OF @MyCursor " +
         " FETCH NEXT FROM @MyCursor INTO @AmazonCategory, @BuyCategory " +
         "END " +
         "CLOSE @MyCursor " +
         "DEALLOCATE @MyCursor " +
         "GO";
      command.CommandType = CommandType.Text;
      rowsAffected = command.ExecuteNonQuery();


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Thursday, December 2, 2010 2:56 PM
  • Server-side cursors are not supported directly by ADO.NET, but you can use them. The below example looks like one method you may want to try:

    http://www.codeproject.com/KB/database/SimulatingRecordsets.aspx

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by KevinBurton Thursday, December 2, 2010 3:26 PM
    Thursday, December 2, 2010 3:13 PM