SQL Incorrect syntax near "," when using parameterized SQL

Question

I'm using Azure to host my database. The most common solutions to this problem I've found all have to do with incorrect data in the SQL query. I'm using parameters so I wouldn't think that would be an issue. My input data doesn't include any characters that SQL would recognize for a query. I'm stumped. Here is my code.

    Public Function camp_UploadScoutRecord(ByVal recordID As String, ByVal requirementsID As String, ByVal scoutID As String, _
                                ByVal scoutName As String, Optional ByVal unitType As String = "", Optional ByVal unitNumber As String = "", Optional ByVal district As String = "", _
                                Optional ByVal council As String = "", Optional ByVal street As String = "", Optional ByVal city As String = "", Optional ByVal campName As String = "", Optional ByVal req1 As String = "", Optional ByVal req2 As String = "", _
                                Optional ByVal req3 As String = "", Optional ByVal req4 As String = "", Optional ByVal req5 As String = "", Optional ByVal req6 As String = "", Optional ByVal req7 As String = "", _
                                Optional ByVal req8 As String = "", Optional ByVal req9 As String = "", Optional ByVal req10 As String = "", Optional ByVal req11 As String = "", Optional ByVal req12 As String = "", _
                                Optional ByVal req13 As String = "", Optional ByVal req14 As String = "", Optional ByVal req15 As String = "", Optional ByVal req16 As String = "", Optional ByVal req17 As String = "", _
                                Optional ByVal req18 As String = "", Optional ByVal req19 As String = "", Optional ByVal req20 As String = "", Optional ByVal req21 As String = "", Optional ByVal req22 As String = "", _
                                Optional ByVal badgeComplete As String = "", Optional ByVal badgeName As String = "", Optional ByVal subscriberID As String = "") As String Implements IMastersheetUpload.camp_UploadScoutRecord

    Dim newRecordID As String
        Dim dateToday As Date = Date.Today
        newRecordID = Guid.NewGuid.ToString()

        Dim selectcmd As New SqlCommand("SELECT * FROM campMeritBadgeRecords WHERE meritBadgeRequirementsID = @ID", myconn)
        Dim sqlParam As New SqlParameter("@ID", newRecordID)
        selectcmd.Parameters.Add(sqlParam)
        Dim ds As New DataSet()
        Dim da As New SqlDataAdapter(selectcmd)
        da.Fill(ds)

        'Find an unused recordID for this record
        'If the GUID already exists in the database, then generate new one
        If ds.Tables(0).Rows.Count <> 0 Then
            While ds.Tables(0).Rows.Count <> 0
                newRecordID = Guid.NewGuid.ToString()
                da.Fill(ds)
            End While
        End If

    Dim insertCMD As New SqlCommand("INSERT INTO campMeritBadgeRecords " + _
                                        "VALUES (@recordID," + _
                                        "@meritBadgeRequirementsID," + _
                                        "@scoutID," + _
                                        "@lastUpdated," + _
                                        "@scoutName," + _
                                        "@unitType," + _
                                        "@unitNumber," + _
                                        "@district," + _
                                        "@council," + _
                                        "@street," + _
                                        "@city," + _
                                        "@req1Complete," + _
                                        "@req2Complete," + _
                                        "@req3Complete," + _
                                        "@req4Complete," + _
                                        "@req5Complete," + _
                                        "@req6Complete," + _
                                        "@req7Complete," + _
                                        "@req8Complete," + _
                                        "@req9Complete," + _
                                        "@req10Complete," + _
                                        "@req11Complete," + _
                                        "@req12Complete," + _
                                        "@req13Complete," + _
                                        "@req14Complete," + _
                                        "@req15Complete," + _
                                        "@req16Complete," + _
                                        "@req17Complete," + _
                                        "@req18Complete," + _
                                        "@req19Complete," + _
                                        "@req20Complete," + _
                                        "@req21Complete," + _
                                        "@req22Complete," + _
                                        "@badgeComplete," + _
                                        "@campName," + _
                                        "@badgeName," + _
                                        "@uploadSubscriberID);", myconn)

        With insertCMD.Parameters
            'Record Info
            .AddWithValue("@recordID", newRecordID)
            .AddWithValue("@meritBadgeRequirementsID", requirementsID)

            'Scout Info
            .AddWithValue("@scoutID", scoutID)
            .AddWithValue("@lastUpdated", Date.Today.ToString)
            .AddWithValue("@scoutName", scoutName)
            .AddWithValue("@unitType", unitType)
            .AddWithValue("@unitNumber", unitNumber)
            .AddWithValue("@district", district)
            .AddWithValue("@council", council)
            .AddWithValue("@street", street)
            .AddWithValue("@city", city)

            'Merit Badge Completion Info
            .AddWithValue("@req1Complete", req1)
            .AddWithValue("@req2Complete", req2)
            .AddWithValue("@req3Complete", req3)
            .AddWithValue("@req4Complete", req4)
            .AddWithValue("@req5Complete", req5)
            .AddWithValue("@req6Complete", req6)
            .AddWithValue("@req7Complete", req7)
            .AddWithValue("@req8Complete", req8)
            .AddWithValue("@req9Complete", req9)
            .AddWithValue("@req10Complete", req10)
            .AddWithValue("@req11Complete", req11)
            .AddWithValue("@req12Complete", req12)
            .AddWithValue("@req13Complete", req13)
            .AddWithValue("@req14Complete", req14)
            .AddWithValue("@req15Complete", req15)
            .AddWithValue("@req16Complete", req16)
            .AddWithValue("@req17Complete", req17)
            .AddWithValue("@req18Complete", req18)
            .AddWithValue("@req19Complete", req19)
            .AddWithValue("@req20Complete", req20)
            .AddWithValue("@req21Complete", req21)
            .AddWithValue("@req22Complete", req22)
            .AddWithValue("@badgeComplete", badgeComplete)
            .AddWithValue("@campName", campName)
            .AddWithValue("@badgeName", badgeName)
            .AddWithValue("@uploadSubscriberID", subscriberID)
        End With

        insertCMD.ExecuteNonQuery()

        myconn.Close()

        'Return recordID to tablet software for future record updates
        Return newRecordID

Use SQLProfiler tool and monitor what query your sql engine executes. — Vimal CK, Jan 14 '14 at 06:11
What happens when a something comes along with a 23rd Requirement (I assume that's what `req` is short for)? It would be far more sensible if these values were being stored in their own table - one row for each Requirement, probably with column names `recordId`, `RequirementOrdinal`, `Complete`. (Or possible skip the `complete` column and have the table represent "completed requirements" - the presence of a particular row indicates completion of that requirement). — Damien_The_Unbeliever, Jan 14 '14 at 07:46
I probably should add some background. This is part of an effort to digitize summer camp merit badge records for a boy scout council. The 22 requirement slots are based off of the paper form that hasn't greatly changed since 1985 or so. The actual requirements worked on are uploaded to a different table to reduce duplicate data. Digitizing the records should save about 150 man-hours of labor every week by not having to fill out the paper forms, but to comply with the national office's requirements we can't differ too much from the original form. — Tonkaman, Jan 14 '14 at 13:40
You should generally design your database around the *data*, rather than around forms (whether those forms be paper based or displayed on screen) — Damien_The_Unbeliever, Jan 14 '14 at 14:07
The only data we care about is what is on the paper forms. The website where it is uploaded to allows scout troops to log-in and print the merit badge forms themselves on cardstock. The printed forms are identical to the existing forms. It ends up on the same forms one way or another. This system is just to speed up the filling out of the forms, and offload the cost of labor and time from the council. If a scout doesn't finish a merit badge at camp, he needs a paper record of what he has already done to take to another badge counselor who may or may not be part of the same council. — Tonkaman, Jan 14 '14 at 14:20

score 1 · Answer 1 · answered Jan 14 '14 at 05:31

1

My guess would be this line...

INSERT INTO campMeritBadgeRecords" + _
"VALUES (@recordID," + _

You aren't leaving a space between campMeritBadgeRecords and VALUES, so SQL Server reads it as

INSERT INTO campMeritBadgeRecordsVALUES(

answered Jan 14 '14 at 05:31

Scottie

11,050
19
68
109

I added the space and I'm still getting the same error. I added more of the code to my question to give a better picture of what it's doing. – Tonkaman Jan 14 '14 at 14:04
@Tonkaman - Your updated code still lacks the space between the table name and the VALUES keyword. – Chris Dunaway Jan 14 '14 at 15:37

score 1 · Accepted Answer · edited Jan 14 '14 at 06:16

1

I think your mistake in insert statement.

Where table name campMeritBadgeRecords and values are combined in insert statement so you have to add extra space after table name campMeritBadgeRecords

So your statement will be like that

Dim insertCMD As New SqlCommand("INSERT INTO campMeritBadgeRecords  values" + _

edited Jan 14 '14 at 06:16

marc_s

732,580
175
1,330
1,459

answered Jan 14 '14 at 05:33

Dharmesh

132
1
12

score 0 · Answer 3 · edited May 23 '17 at 12:13

The first thing I would do is double check to make sure that the values for parameters are valid sql-convertible datatypes. For example, a Nothing value in your parameters can throw off the entire query, since VB.NET's Nothing does not get implicitly converted to SQL's NULL (we have DBNull for that). If you are not using an ORM (which it looks like you are not) then you can either use the coalescing If() operator or wrap If statements around any potentially null values (Strings, for example).

For example:

With insertCMD.Parameters
    ' coalesce example
    .AddWithValue("@district", If(district, DBNull.Value))

    ' If ... Then example
    If req2 IsNot Nothing Then
        .AddWithValue("@council", council)
    Else
        .AddWithValue("@council", DbNull.Value)
    End If    
End With

As others have pointed out, there could also be a problem with the formatting somewhere in that multi-lined query. To make it easier to read, you can switch it up to use XML literal syntax as found in the answers to this question: Multiline strings in VB.NET

Dim sqlString = _
    <![CDATA[
        INSERT INTO campMeritBadgeRecords 
        VALUES (
            @recordID, 
            @meritBadgeRequirementsID, 
            @scoutID, 
            @lastUpdated, 
            @scoutName, 
            @unitType, 
            @unitNumber, 
            @district, 
            @council, 
            @street, 
            @city, 
            @req1Complete, 
            @req2Complete, 
            @req3Complete, 
            @req4Complete, 
            @req5Complete, 
            @req6Complete, 
            @req7Complete, 
            @req8Complete, 
            @req9Complete, 
            @req10Complete, 
            @req11Complete, 
            @req12Complete, 
            @req13Complete, 
            @req14Complete, 
            @req15Complete, 
            @req16Complete, 
            @req17Complete, 
            @req18Complete, 
            @req19Complete, 
            @req20Complete, 
            @req21Complete, 
            @req22Complete, 
            @badgeComplete, 
            @campName, 
            @badgeName, 
            @uploadSubscriberID
        );
    ]]>.Value

Dim insertCMD As New SqlCommand(sqlString, myconn)

SQL Incorrect syntax near "," when using parameterized SQL

3 Answers3