CFF KB - Carrz-Fox-Fire Promotions Knowledge Base

 CFF KB is all about 1 thing: The Sharing of Knowledge and the Power we gain from it.
  • Breadrumbs:

  • Microsoft OLE DB Provider for SQL Server (0x80040E21) Multiple-step OLE DB operation generated errors

    • CFF Knowledge Base - Share With Facebook CFF Knowledge Base - Share on Twitter CFF Knowledge Base - Share on Reddit CFF Knowledge Base - Share on Digg It CFF Knowledge Base - Share on Stumble Upon It CFF Knowledge Base - Share on Delicious
    Share With Friends (Updated 6-8-2010)
  • Article ID:
    7295
  • Date Created
    Thursday, February 26, 2026
  • Last Updated
    Thursday, February 26, 2026
  • This Article Has been Viewed
    0 times
  • Short Desc

    Using SQL Server Management Studio to generate scripts with comments cannot be transferred over into a single line of code.
  • Article Details

    When creating your SQL Queries inside SQL Server Management Studio (SSMS) or other programs, you might add notes to yourself or to others explaining what a certain part does. These notes, called 'Comments', are surrounded by double dashes.
    These comments are fine in a multi-line query, whether in SSMS or on your website.
    However, they do not cross over on a single line of code.
  • Recreate Issue

    The following example demonstrates code with notes (comments) in it.

     

    UPDATE TableOne
    SET Col3 = CASE
    WHEN Col3 = 3 THEN 4
    WHEN Col3 = 1 THEN 3
    WHEN Col1 = ? THEN 1 -- Parameter 1: strCol1
    ELSE Col3
    END
    WHERE Col2 = ? -- Parameter 2: strCol2
    AND (Col3 IN (1, 3) OR Col1 = ?); -- Parameter 3: strCol1


    The above works as expected in SSMS.
    Taking the same code above and condensing it into a single line for your query will look like this.

    <%
    sql.commandtext="UPDATE TableOne SET Col3 = CASE WHEN Col3 = 3 THEN 4 WHEN Col3 = 1 THEN 3 WHEN col1 = ? THEN 1 -- Parameter 1: strcol1 ELSE Col3 END WHERE col2 = ? -- Parameter 2: strcol2 AND (Col3 IN (1, 3) OR col1 = ?); -- Parameter 3: strcol1"
    sql.Parameters.Append sql.CreateParameter("@col1", adVarChar, adParamInput, 25, getCol1)
    sql.Parameters.Append sql.CreateParameter("@col2", adVarChar, adParamInput, 25, getCol2)
    sql.Parameters.Append sql.CreateParameter("@col1", adVarChar, adParamInput, 25, getCol1)

    %>

  • Resolve Issue

    What is happening with the single-line code above is that the comments are still present. Everything after the comment is now void
    To fix this issue, remove the comments from your code.

    <%
    sql.commandtext="UUPDATE TableOne SET Col3 = CASE WHEN Col3 = 3 THEN 4 WHEN Col3 = 1 THEN 3 WHEN Col1 = ? THEN 1 ELSE Col3 END WHERE Col2 = ? AND (Col3 IN (1, 3) OR Col1 = ?)"
    sql.Parameters.Append sql.CreateParameter("@col1", adVarChar, adParamInput, 25, getCol1)
    sql.Parameters.Append sql.CreateParameter("@col2", adVarChar, adParamInput, 25, getCol2)
    sql.Parameters.Append sql.CreateParameter("@col1", adVarChar, adParamInput, 25, getCol1)

    %>



    --------
    Related Articles

    Microsoft OLE DB Provider for SQL Server (0x80040E21) Multiple-step OLE DB operation generated error«

Recent Articles

All Topics

  • Coming Soon - Knowledge Exchange

Trending Articles