KB Issue

While editing sql statements, mistakes are easily made, an in this case, we missed a parameter that needed to be removed with its corresponding variable.

Issue Details

When writing and editing your SQL Statements, you may inadvertently add or remove a parameter causing the following error.

Microsoft OLE DB Driver for SQL Server error '80040e14'

There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

/Inserts.asp, line 46

Recreate Issue

The following code will reproduce the error, and it is clear to see what is causing it.

[Classic ASP - Wrong number of Values vs Columns]

CFFCS | CarrzSynEdit: | ASP/VBScript

Set InsertStart = CreateObject("ADODB.Command")
InsertStart.ActiveConnection=siteConn
InsertStart.Prepared = true
InsertStart.commandtext = "insert into ATime (ID, StartDate)values(?,?,?)"
InsertStart.Parameters.Append InsertStart.CreateParameter("@ID", adInteger, adParamInput, , strID)
InsertStart.Parameters.Append InsertStart.CreateParameter("@StartDate", adVarChar, adParamInput, 5, getTenure)
InsertStart.execute

Resolve Issue

In this example, it is an easy fix. Remove or add a parameter, and in this case, we are going to remove one of the Question Marks.

[Classic ASP - Equal number of Values vs Columns]

CFFCS | CarrzSynEdit: | ASP/VBScript

Set InsertStart = CreateObject("ADODB.Command")
InsertStart.ActiveConnection=siteConn
InsertStart.Prepared = true
InsertStart.commandtext = "insert into ATime (ID, StartDate)values(?,?)"
InsertStart.Parameters.Append InsertStart.CreateParameter("@ID", adInteger, adParamInput, , strID)
InsertStart.Parameters.Append InsertStart.CreateParameter("@StartDate", adVarChar, adParamInput, 5, getTenure)
InsertStart.execute