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:
    76
  • Date Created
    Wednesday, December 22, 2010
  • Last Updated
    Sunday, June 25, 2023
  • This Article Has been Viewed
    2964 times
  • Short Desc
    When you write your SQL INSERT, UPDATE statement and forget to add in a Column that your Parameters call for, you will receive the following error.
  • Details
    Using Parameterized Queries, you must line up your Parameters with your Columns in your Update or Insert statements.
    If you have more Parameters than Columns, you will get the following error.

     
    Microsoft OLE DB Provider for SQL Server (0x80040E21)
    Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
    /reg.asp, line 350
  • Recreate Issue
    Scenario #1

    <%
    sql.commandtext="insert into table1 (col1, col2, col3, col4)values(?,?,?,?)"
    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("@col3", adVarChar, adParamInput, 25, getCol3)
    sql.Parameters.Append sql.CreateParameter("@col4", adVarChar, adParamInput, 25, getCol4)
    sql.Parameters.Append sql.CreateParameter("@col5", adVarChar, adParamInput, 25, getCol5)
    %>

    As you can see in the above SQL Parameterized Query, we have a slight problem with the number of columns versus the number of parameters assigned.

    Scenario #2
    <%
    sql.commandtext="insert into table1 (col1, col2, col3, col4)values(?,?,?)"
    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("@col3", adVarChar, adParamInput, 25, getCol3)
    sql.Parameters.Append sql.CreateParameter("@col4", adVarChar, adParamInput, 25, getCol4)
    %>

    In the above, we notice we only have 4 Question Marks, yet we have 4 columns and 4 parameters.

    Scenario #3

    <%

    sql.commandtext="update table1 set col1=?, col2=?, col3=?, col4=? where col5"
    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("@col3", adVarChar, adParamInput, 25, getCol3)
    sql.Parameters.Append sql.CreateParameter("@col4", adVarChar, adParamInput, 25, getCol4)
    sql.Parameters.Append sql.CreateParameter("@col5", adVarChar, adParamInput, 25, getCol5)
    %>


    As you can see in the above UPDATE Statement, we are missing the =? on the last col5.

    Scenario #4

    <%

    sql.commandtext="update table1 set col1=?, where col2"
    sql.Parameters.Append sql.CreateParameter("@col1", adVarChar, adParamInput, 25, getCol1)
    sql.Parameters.Append sql.CreateParameter("@col2", adVarhar, adParamInput, 25, getCol2)

    %>


    Looking at the above, our error will fall on the 2nd line of assigned parameters.
    We have a misspelling error that causes the error to appear.
    adVarhar should be adVarChar

  • Resolve Issue
    Scenario #1
    To correct this issue, ensure you have a Parameter for each column and vice-versa.
    Everything must match for your code to work correctly.

    <%
    sql.commandtext="insert into table1 (col1, col2, col3, col4, col5)values(?,?,?,?,?)"
    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("@col3", adVarChar, adParamInput, 25, getCol3)
    sql.Parameters.Append sql.CreateParameter("@col4", adVarChar, adParamInput, 25, getCol4)
    sql.Parameters.Append sql.CreateParameter("@col5", adVarChar, adParamInput, 25, getCol5)
    %>


    With the above code, you will notice that each column in the SQL Statement has its matching Parameter to go along with it.

    Scenario #2
    <%
    sql.commandtext="insert into table1 (col1, col2, col3, col4)values(?,?,?,?)"
    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("@col3", adVarChar, adParamInput, 25, getCol3)
    sql.Parameters.Append sql.CreateParameter("@col4", adVarChar, adParamInput, 25, getCol4)
    %>

    The same with Scenario #1, we now have a matching set of everything. Adding in the extra Question Mark completes this code.

    Scenario #3
    This can also happen with an UPDATE Statement as well.

    <%

    sql.commandtext="update table1 set col1=?, col2=?, col3=?, col4=? where col5=?"
    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("@col3", adVarChar, adParamInput, 25, getCol3)
    sql.Parameters.Append sql.CreateParameter("@col4", adVarChar, adParamInput, 25, getCol4)
    sql.Parameters.Append sql.CreateParameter("@col5", adVarChar, adParamInput, 25, getCol5)
    %>


    As you can see in the above example, we added the =? to our last col5
    Which corrects the issue and allows the code to run.

    Scenario #3

    <%

    sql.commandtext="update table1 set col1=?, where col2"
    sql.Parameters.Append sql.CreateParameter("@col1", adVarChar, adParamInput, 25, getCol1)
    sql.Parameters.Append sql.CreateParameter("@col2", adVarchar, adParamInput, 25, getCol2)

    %>


    In the above example, we correct the spelling error and our code runs.

Recent Articles

All Topics

  • Coming Soon - Knowledge Exchange

Trending Articles