Insert Failed Strange Error

[l310564]

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


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