KB Issue
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.
Issue 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
[Classic ASP - Scenario #1]
CFFCS | CarrzSynEdit: | ASP/VBScript 
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 SQL Parameterized Query above, we have a slight mismatch between the number of columns and the number of parameters.
[Classic ASP - Scenario #2]
CFFCS | CarrzSynEdit: | ASP/VBScript 
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.
[Classic ASP - Scenario #3]
CFFCS | CarrzSynEdit: | ASP/VBScript 

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 UPDATE Statement above, we are missing the =? in the last column (col5).
[Classic ASP - Scenario #4]
CFFCS | CarrzSynEdit: | ASP/VBScript 

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 that causes this error.
adVarhar should be adVarChar
Resolve Issue
To correct this issue, ensure you have a Parameter for each column and vice versa.
Everything must match for your code to work correctly.
[Classic ASP - ]
Scenario #1
CFFCS | CarrzSynEdit: | ASP/VBScript 
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)

In the code above, you will notice that each column in the SQL Statement has a matching Parameter.
[Classic ASP - Scenario #2]
CFFCS | CarrzSynEdit: | ASP/VBScript 
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)

As in Scenario #1, we now have a matching set of everything. Adding in the extra Question Mark completes this code.
This can also happen with an UPDATE statement.
[Classic ASP - Scenario #3]
CFFCS | CarrzSynEdit: | ASP/VBScript 

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.
[Classic ASP - Scenario #4]
CFFCS | CarrzSynEdit: | ASP/VBScript 

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.
Other Articles Related to this Entry.Microsoft OLE DB Provider for SQL Server (0x80040E21) Multiple-step OLE DB operation generated error«