Getting error when trying to use sqlclient.sqldatareader?

Question

I am getting error saying that my syntax is incorrect however everything looks fine to me.

On the line where datareader is used it says that there is incorrect syntax at the word "And".

I have tried numerous changes to try make it work with no avail. any help?

SQL:-

Dim sqlline1 As String = String.Format("Select count(debtor_id) As 
numbernew, sum(debtor_original_debt) As valuenew, SUM(debtor_balance)
From debtors Where debtor_cohort_id Like {0} + {2} And (debtor_client_id >= 
{0} And debtor_client_id <= {1})", clientid_start, clientid_end, Cohort4)

function it errors on:-

Public Function populatestringwithsql(command As String) As String
    Dim conn As String = "a server connection"
    Dim i = 1
    Dim fields(15) As String
    Dim numberoffields As Integer
    Using connObj As New SqlClient.SqlConnection(conn)
        Using cmdObj As New SqlClient.SqlCommand(command, connObj)
            connObj.Open()
            Dim readobj As SqlClient.SqlDataReader = cmdObj.ExecuteReader()
            Dim schematable As DataTable = readobj.GetSchemaTable()
            readobj.Close()

            For Each dr As DataRow In schematable.Rows
                numberoffields = schematable.Columns.Count
                For Each dc As DataColumn In schematable.Columns

                    fields(i) = dc.ColumnName
                    i += 1
                Next
            Next

            Using readerObj As SqlDataReader = cmdObj.ExecuteReader
                'This will loop through all returned records 
                While readerObj.Read
                    If fields(1) <> "" Then
                        fields(1) = readerObj(fields(1)).ToString
                    End If
                    If fields(2) <> "" Then
                        fields(2) = readerObj(fields(2)).ToString
                    End If
                    If fields(3) <> "" Then
                        fields(3) = readerObj(fields(3)).ToString
                    End If
                    If fields(4) <> "" Then
                        fields(4) = readerObj(fields(4)).ToString
                    End If
                    If fields(5) <> "" Then
                        fields(5) = readerObj(fields(5)).ToString
                    End If
                    If fields(6) <> "" Then
                        fields(6) = readerObj(fields(6)).ToString
                    End If
                    If fields(7) <> "" Then
                        fields(7) = readerObj(fields(7)).ToString
                    End If
                    If fields(8) <> "" Then
                        fields(8) = readerObj(fields(8)).ToString
                    End If
                    If fields(9) <> "" Then
                        fields(9) = readerObj(fields(9)).ToString
                    End If
                    If fields(10) <> "" Then
                        fields(10) = readerObj(fields(10)).ToString
                    End If
                    If fields(11) <> "" Then
                        fields(11) = readerObj(fields(11)).ToString
                    End If
                    If fields(12) <> "" Then
                        fields(12) = readerObj(fields(12)).ToString
                    End If
                    If fields(13) <> "" Then
                        fields(13) = readerObj(fields(13)).ToString
                    End If
                    If fields(14) <> "" Then
                        fields(14) = readerObj(fields(14)).ToString
                    End If
                    If fields(15) <> "" Then
                        fields(15) = readerObj(fields(15)).ToString
                    End If
                End While
            End Using
            connObj.Close()
        End Using
    End Using

    Dim stringend As Text.StringBuilder

    For Each field In fields
        stringend.Append(field)

    Next

    Return stringend.ToString

End Function

I have tried all debugging avenues and had no luck.

What version and flavor of SQL? I don't think you can use curly brackets for literals in most SQL. Hence why you are getting an error at "and". — Jacob H, Aug 24 '17 at 13:21
What values are you putting into those String.Format variables? I suspect it has something to do with missing single quotes... — soohoonigan, Aug 24 '17 at 13:21
@Jacob H those curly braces are for substituting in strings with String.Format() — soohoonigan, Aug 24 '17 at 13:22
wow! soohoonigan thats the reason, im such an idiot! its the bloody single quotes!! — Danny James, Aug 24 '17 at 13:26

score 1 · Accepted Answer · answered Aug 24 '17 at 13:31

Looking at Where debtor_cohort_id Like {0} + {2} And ... it looks like you are trying to concatenate client_id and cohort4. Unless client_id or cohort4 contain a wildcard character use = rather than LIKE.

You probably need single quotes around your like comparison Where debtor_cohort_id Like '{0} + {2}' And ...

The easiest thing is to print your sqlline1 variable to reveal exactly what is wrong with it.

Getting error when trying to use sqlclient.sqldatareader?

1 Answers1