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

[l310564]

Hello,

I have an application that converts one database to another. This application has multipule threads all inserting diffrent data into the new database. The code 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.


As far as I can see this isn't a coding issue as all it is doing is inserting null in to all fields in a table and the code is exactly the same each time it executes, and it executes fine a number of times before it fails. For this reason I have to look at Sql Server and ask if there is any other reason why it would produce this error apart from the obvious?

The application creates a new connection each time it does an insert and then closes the connection once it has finished. As a result the application puts alot of pressure on the sql server and will use 100% of the sql servers processing power along with most of the ram. Is this error produced when sql server can't handle a query for some reason?

Any ideas would be welcome as I have spent three days on this and got no where!


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

Isaac Asimov

 

[bborissov]

How your INSERT command look like?


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[l310564]

Thanks for the quick reply Borislav. Below is the Vb.net code used to create the command and execute it. Just to double check here is what the variable sql equles at the point of error :

INSERT INTO notes (RefID,TypeID,DateEntered,Username,[Subject],Body,DoNotSendToWeb,Deleted,SubTypeID) VALUES (@RefID,@TypeID,@DateEntered,@Username,@Subject,@Body,@DoNotSendToWeb,@Deleted,@SubTypeID)



[/i] 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[/i]

Thanks,

Hugh

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

Isaac Asimov

 

[l310564]

Just to make double double sure I got rid of the parms and executed the following sql
INSERT INTO notes (RefID,TypeID,DateEntered,Username,[Subject],Body,DoNotSendToWeb,Deleted,SubTypeID) VALUES (0,0,null,null,null,null,null,null,null)


It failed with the same error after 129 successfull inserts

cheers,

Hugh



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

Isaac Asimov

 

[gmmastros]

Are there any triggers on the notes table?

Sp_helptrigger 'notes'


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[l310564]

nope only thing that exists on that table is a PK with an auto increment identity called ID



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

Isaac Asimov

 

[l310564]

Solved it after three days.......


Something funny was happening because my application was multi threaded. It was returning an error in the wrong place and actually related to another SQL query being executed else where in the code.



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

Isaac Asimov