Mobile .NET Code Problems Involving sqlCe

[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.

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

 

[ThatRickGuy]

I've never worked with ceSQL, but are you sure the parameters are properly merging over the "?"s? Also, why bother with the complexe subquery and join when you are only inserting those 4 values?

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.