There are fewer columns in the INSERT isn't the answer

Question

Hello,

I have an application that converts one database to another and one of the functions I use is insert_Note below. This application has multipule threads all inserting diffrent data into the new database. The code below works for a number of inserts ranging between 24 and 120 but then fails with the error:

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.

I you look at the code you will see that this cannot be the problem as the code works for some inserts but not all. What else can cause this error in a multithread application? Any ideas would be welcome as I have spent three days on this and got no where!

*Public sub insert_Note
    Dim sql As String
    Dim cmd As New SqlClient.SqlCommand
    sql = "INSERT INTO notes ("
    sql &= "RefID,"
    sql &= "TypeID,"
    sql &= "DateEntered,"
    sql &= "Username,"
    sql &= "Subject,"
    sql &= "Body,"
    sql &= "DoNotSendToWeb,"
    sql &= "Deleted,"
    sql &= "SubTypeID"
    sql &= ")  VALUES ("
    sql &= "@RefID,"
    sql &= "@TypeID,"
    sql &= "@DateEntered,"
    sql &= "@Username,"
    sql &= "@Subject,"
    sql &= "@Body,"
    sql &= "@DoNotSendToWeb,"
    sql &= "@Deleted,"
    sql &= "@SubTypeID"
    sql &= ")"
    cmd.CommandText = sql
    cmd.Parameters.AddWithValue("@RefID", 0)
    cmd.Parameters.AddWithValue("@TypeID", 0)
    cmd.Parameters.AddWithValue("@DateEntered", DBNull.Value)
    cmd.Parameters.AddWithValue("@Username", DBNull.Value)
    cmd.Parameters.AddWithValue("@Subject", DBNull.Value)
    cmd.Parameters.AddWithValue("@Body", DBNull.Value)
    cmd.Parameters.AddWithValue("@DoNotSendToWeb", DBNull.Value)
    cmd.Parameters.AddWithValue("@Deleted", DBNull.Value)
    cmd.Parameters.AddWithValue("@SubTypeID", DBNull.Value)
    Using DotNetdatabase As New DBAccessLayer.DBAccessLayer(VBDotNetConn)
        Try
            Dim ds As DataSet
            ds = DotNetdatabase.OpenDataSetWithoutErrorMsg(cmd)
            Return (0)
        Catch ex As Exception
         AddToErrorLog(ex.Message, "Adding Note")
            Throw ex
        End Try
    End Using
End Function

public class DBAccessLayer
    private myConnection as SqlConnection
    Public Sub New(ByVal strConnectionString As String)
          myConnection = New SqlConnection(strConnectionString)
    End Sub
     Public Function OpenDataSetWithoutErrorMsg(ByVal myCommand As SqlCommand) As DataSet
            Dim MyDataSet As New DataSet
            Dim myDataAdapter As SqlDataAdapter
            Try
                Dim blnInitiallyOpen As Boolean = True

                    myConnection.Open()
                    Do Until myConnection.State = ConnectionState.Open
                            Threading.Thread.Sleep(100)
                    Loop

                    myCommand.CommandTimeout = 0
                    myCommand.Connection = myConnection
                    myDataAdapter = New SqlDataAdapter(myCommand)
                    myDataAdapter.Fill(MyDataSet)

                    myConnection.Close()

                    Return MyDataSet
            Catch e As Exception
                    If myConnection.State = ConnectionState.Open Then
                        myConnection.Close()
                    End If
                    Throw e
                   Return Nothing
               End Try
    End Function
end class*

Thanks,

Hugh If knowlege can create problems it is not through ignorance that we will solve them.

Isaac Asimov

Answer 1

Hugh:

How I might go about solving this problem:

1) in your catch, specifically catch the error concerning fewer columns in the insert. Then log the command text and parameter values / or manually investigate. If you don't see your problem there, then post the results of that logging to your question. If you do see the problem, then choose my answer :) --> I wonder, what if a parameter such as @Body were to be an empty string, instead of null? What would the insert statement look like?

2) I'm a db programmer. This code belongs in a stored procedure. Much easier to debug.

3) Have you been able to narrow the occurrence down to a set of data?

4) Go with Yellowfog's logic - is there a trigger, or just as importantly - some other piece of code being fired along the connection - happening in sequence that you might be generating the error, but once bubbled up to where ever you are seeing it makes it appear to be this code? I know that often when I'm sure that a piece of code couldn't cause a particular exception, I'm right, I've just been looking at the wrong code.

5) Use a sql server profiler to see what statements are actually being executed. The events I would add are: TSQL-SQL:StmtCompleted & SQL:StmtStarting, Errors and Warnings: All of them. It will be easiest to do this on a box with as little traffic as possible. This page might help you get started.