Problem Trying to Insert Results into a sqlCE DB

[chadau]

I am attempting to do an INSERT RESULTS into a sqlCe database table, but receive exception stating that "There was an error parsing the query. [Token line number,Token line offset,,Token in error,,]". Code in question is shown below.


CODE
Friend Sub InsertAllScoringDetail(ByVal headerID As String, ByVal productCode As String)
Dim sb As New StringBuilder
Dim cmd As SqlCeCommand = Nothing

Try
If Me.cnn.State = ConnectionState.Closed Then
Me.cnn.Open()
End If

cmd = Me.cnn.CreateCommand()

With sb
.Append("INSERT INTO ScoringDetails ")
.Append("(CriteriaID, CategoryID, HeaderID, Points, Quantity, FollowUp) ")
.Append("(SELECT CriteriaReference.ID, CriteriaReference.CategoryID, ? AS HeaderID, ")
.Append("? AS Points, ? AS Quantity, ? AS FollowUp ")
.Append("FROM CriteriaReference INNER JOIN ")
.Append("Category ON CriteriaReference.CategoryID = Category.ID INNER JOIN ")
.Append("ProductGroupReference ON Category.ProductGroupCode = ProductGroupReference.GroupCode ")
.Append("WHERE ProductGroupReference.ProductCode = ?))")
End With

cmd.CommandText = sb.ToString

cmd.Parameters.Add("@HeaderID", SqlDbType.NVarChar)
cmd.Parameters("@HeaderID").Size = 7
cmd.Parameters("@HeaderID").Value = headerID

cmd.Parameters.Add("@Points", SqlDbType.SmallInt)
cmd.Parameters("@Points").Value = 0

cmd.Parameters.Add("@Quantity", SqlDbType.SmallInt)
cmd.Parameters("@Quantity").Value = 0

cmd.Parameters.Add("@FollowUp", SqlDbType.Bit)
cmd.Parameters("@FollowUp").Value = System.DBNull.Value

cmd.Parameters.Add("@ProductCode", SqlDbType.NVarChar)
cmd.Parameters("@ProductCode").Size = 7
cmd.Parameters("@ProductCode").Value = productCode

cmd.ExecuteNonQuery()
Catch ex As SqlCeException
ShowErrors(ex)
Catch ex As Exception
ShowErrors(ex)
Finally
cmd.Dispose()
End Try

 

[rac2]

Looks like you have an extra parenthesis in this line of code

 .Append("WHERE ProductGroupReference.ProductCode = ?[COLOR=red])[/color])")

I wonder about the names you have given to the parameters. I would not use a special symbol such as @ in a name. I believe the names supplied to the Parameters.Add() method are merely the ones you wish to use when retrieving OUTPUT values. Sometimes a stored procedure is called by a command object and the parameters match those used in the stored procedure. Which have names like @ProductCode. But that is a hole different thing. So your code may well work but I just wouldnt use those names.