How to fix error "SQLexception was unhandled by user code incorrect syntax near comma

Question

Any help on where the ',' error is would be greatly appreciated!

Here is my VB code:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.ArgumentException

Partial Class MeetingAdd
    Inherits System.Web.UI.Page

    Public Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click

        Dim strConnection As String = ConfigurationManager.ConnectionStrings("ServiceLine_CommitteeConnectionString").ConnectionString
        Dim con As New SqlConnection(strConnection)
        Dim cmd As New SqlCommand("insert into ServiceLine_Committee.DBO.tblVotings(Meeting_ID, Committee_ID, Member_Name) , " +
                                       "SELECT C.ID, M.ID, CM.FULL_NAME FROM ServiceLine_Committee.DBO.tblCommitteeName C, " +
                                       "ServiceLine_Committee.DBO.tblMeetings M, ServiceLine_Committee.DBO.tblCommitteeMembers CM " +
                                       "WHERE M.ContractCategory    = C.Contract_Category, " +
                                       "AND M.Committee             = C.Committee_Name, " +
                                       "AND CM.Committee            = M.Committee", con)
        cmd.Connection = con
        cmd.CommandType = CommandType.Text
        con.Open()

        Dim result As Integer = cmd.ExecuteNonQuery()

        con.Close()

    End Sub

End Class

Please help. This is very frustrating.

@Codexer according to this post http://stackoverflow.com/questions/288794/does-c-sharp-optimize-the-concatenation-of-string-literals contatenating string literals does not incur the same performance penalty as contatenating in a loop or separate lines. It's for c# but it implies VB works this way too. Unless you mean the OP should use & — Jeremy, Sep 14 '15 at 20:20

A_Sk · Accepted Answer · 2015-09-14T20:11:19.050

In your posted Code you used unusual ',' multiple times.

1> 1st line here:Member_Name) ,

2>4th line here "WHERE M.ContractCategory = C.Contract_Category, " + "AND M.Committee = C.Committee_Name,

Try:

  Dim cmd As New SqlCommand("insert into ServiceLine_Committee.DBO.tblVotings(Meeting_ID, Committee_ID, Member_Name) " +
                                       " SELECT C.ID, M.ID, CM.FULL_NAME FROM ServiceLine_Committee.DBO.tblCommitteeName C, " +
                                       "ServiceLine_Committee.DBO.tblMeetings M, ServiceLine_Committee.DBO.tblCommitteeMembers CM " +
                                       " WHERE M.ContractCategory   = C.Contract_Category " +
                                       "AND M.Committee             = C.Committee_Name " +
                                       "AND CM.Committee            = M.Committee", con)

or

  Dim cmd As New SqlCommand("insert into ServiceLine_Committee.DBO.tblVotings(Meeting_ID, Committee_ID, Member_Name) " +
                                               " SELECT C.ID, M.ID, CM.FULL_NAME FROM ServiceLine_Committee.DBO.tblCommitteeName C INNER JOIN  " +
                                               "ServiceLine_Committee.DBO.tblMeetings M ON C.Contract_Category=M.ContractCategory AND C.Committee_Name=M.Committee INNER JOIN ServiceLine_Committee.DBO.tblCommitteeMembers CM ON CM.Committee=M.Committee", con)

Edit2:

String concatenation using + sign is not good,you can use Escaping in verbatim strings

Dim cmd As New SqlCommand(@"insert into ServiceLine_Committee.DBO.tblVotings
(Meeting_ID, Committee_ID, Member_Name)  SELECT 
C.ID, M.ID, CM.FULL_NAME   FROM   ServiceLine_Committee.DBO.tblCommitteeName C INNER JOIN ServiceLine_Committee.DBO.tblMeetings M ON  C.Contract_Category=M.ContractCategory AND 
C.Committee_Name=M.Committee INNER JOIN ServiceLine_Committee.DBO.tblCommitteeMembers CM ON 
CM.Committee=M.Committee", con)

FROM ?? can you tell me, where??i can't find where i missed. @Jeremy — A_Sk, Sep 14 '15 at 20:14
never mind. the tables are separated by commas too (which is discouraged by the way) and I didn't see the From word. So, I was missing something :) — Jeremy, Sep 14 '15 at 20:18

score 0 · Answer 2 · answered Sep 14 '15 at 20:35

The error itself is clear: Its saying You have an extra comma that SQL doesnt understand syntax for. The best way to debug this is set your SQLCommandString to a variable like mySQLCommand

This way you can see what the actual SQL command is being executed. Put a break point in right after you set mySQLCommand'. Copy that text directly into a Query fromServer Exploreror intoSQL Server Management Studio` and try to execute it. You will find it failing. Now you can get the query correct and re-paste it back in your code.

A few other notes: You are selecting from multiple tables but you are not clearly defining a relationship. For example:

SELECT Name, Car FROM Names, Cars

Technically will work. But this is poorly written. A better way is to define how Names and Cars are related. Something like this:

SELECT n.name, c.car FROM Names n
JOIN cars c on c.nameid = n.nameid

How to fix error "SQLexception was unhandled by user code incorrect syntax near comma

2 Answers2