Running stored procedure in vb.net results in "invalid column name"

Question

I am trying to execute a stored procedure with parameters in a vb.net application, and keep getting an error

System.Data.SqlClient.SqlException: 'Invalid column name 'r'

I tried to find a solution but a lot of the questions seemed to be around inserting data, whereas I am only trying to read data.

I'm simply not sure where it is picking up that I am trying to reference 'r' as a column name? The results are exported to a DataGrid box on the form.

Code:

Private Function GetResults() As DataTable
    Dim dtAccounts As New DataTable
    Dim connString As String = ConfigurationManager.ConnectionStrings("PBSQL").ConnectionString
    Dim CountDoc As String = "rqs.service_id,svc.service_abbrev,do.doctor_id,do.dr_first_name,do.dr_surname"
    Dim NoCountDoc As String = "rqs.service_id,svc.service_abbrev"

    Using conn As New SqlConnection(connString)
        Using cmd As New SqlCommand("EXEC dbo.aslGetCommonv1 @StartDate, @EndDate, @GroupBy", conn)

            cmd.Parameters.Add("@StartDate", SqlDbType.Date).Value = dateFrom.Value
            cmd.Parameters.Add("@EndDate", SqlDbType.Date).Value = dateTo.Value

            If cb_SearchByDoc.Checked = True Then
                cmd.Parameters.Add("@GroupBy", SqlDbType.VarChar).Value = CountDoc
            Else
                cmd.Parameters.Add("@GroupBy", SqlDbType.VarChar).Value = NoCountDoc
            End If

            conn.Open()

            Dim reader As SqlDataReader = cmd.ExecuteReader()
            dtAccounts.Load(reader)

        End Using
    End Using

Process:
    Return dtAccounts

End Function

Private Sub BtnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
    dgResults.DataSource = GetResults()
End Sub

Here is the procedure in SQL:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[aslGetCommonv1]
    @StartDate varchar,
    @EndDate varchar,
    @GroupBy varchar
AS
    DECLARE @SQL VARCHAR(MAX)
    SET NOCOUNT ON;
    SET @SQL = 'SELECT rqs.service_id,svc.service_abbrev,do.doctor_id,do.dr_first_name,do.dr_surname,
    COUNT(*) AS total
    FROM dbo.requests_services rqs
    LEFT JOIN services svc ON svc.service_id = rqs.service_id
    LEFT JOIN requests rq ON rq.request_id = rqs.request_id
    LEFT JOIN doctors do ON do.doctor_id  = rq.doctor_id
    WHERE rq.entry_date BETWEEN ' + @StartDate + ' AND ' + @EndDate + ' AND svc.service_id BETWEEN 1005 And 1008 Or svc.service_id = 1401 Or svc.service_id = 1402 Or svc.service_id = 1404 Or svc.service_id = 1405
        OR svc.service_id = 1407 OR svc.service_id = 1408 OR svc.service_id = 1410 OR svc.service_id = 1501 OR svc.service_id BETWEEN 1503 AND 1506 OR svc.service_id = 1509
        OR svc.service_id BETWEEN 5101 AND 5103 OR svc.service_abbrev = ''SADREN'' OR svc.service_abbrev = ''SADREN2'' OR svc.service_abbrev = ''SFH28'' OR svc.service_abbrev = ''SLPSAL''
        OR svc.service_abbrev = ''TFT'' OR svc.service_abbrev = ''CTA'' OR svc.service_abbrev = ''VITD'' OR svc.service_abbrev = ''U216 OH'' OR svc.service_abbrev = ''U2416 OH''
        OR svc.service_abbrev = ''UEMET'' OR svc.service_abbrev = ''CALP'' OR svc.service_abbrev = ''FAEPCR'' OR svc.service_abbrev = ''CDSA1'' OR svc.service_abbrev = ''CDSA2'' 
        OR svc.service_abbrev = ''CDSA3'' OR svc.service_abbrev = ''CDSA3+'' OR svc.service_abbrev = ''CDSA4'' OR svc.service_abbrev = ''CDSA4+'' OR svc.service_abbrev = ''CDSA5''
        OR svc.service_abbrev = ''INTPERM'' OR svc.service_abbrev = ''SIBO2'' OR svc.service_abbrev = ''SIBO3'' OR svc.service_abbrev = ''GIEFFCO'' OR svc.service_abbrev = ''GIEFFME'' OR svc.service_abbrev = ''CMAP''
        OR svc.service_abbrev = ''CMMR'' OR svc.service_abbrev = ''IGA96'' OR svc.service_abbrev = ''IGA96AS'' OR svc.service_abbrev = ''IGG144'' OR svc.service_abbrev = ''IGA144'' OR svc.service_abbrev = ''IGG208''
        OR svc.service_abbrev = ''IGA208'' OR svc.service_abbrev = ''IGG+A208'' OR svc.service_abbrev = ''ALC050'' OR svc.service_abbrev = ''ALC100'' OR svc.service_abbrev = ''ALC150'' OR svc.service_abbrev = ''ALC200''
        OR svc.service_abbrev = ''ALCPLAT'' OR svc.service_abbrev = ''CYTOK'' OR svc.service_abbrev = ''HIAM'' OR svc.service_abbrev = ''DAO'' OR svc.service_abbrev = ''KRPR'' OR svc.service_abbrev = ''ENEUM'' OR svc.service_abbrev = ''LIPOSCRN''
        OR svc.service_abbrev = ''INEUM'' OR svc.service_abbrev = ''ANEUM'' OR svc.service_abbrev = ''ORGANIX'' OR svc.service_abbrev = ''UIODL'' OR svc.service_abbrev = ''MTHFR'' OR svc.service_abbrev = ''OM3INPR''
        OR svc.service_abbrev = ''ONCOSTAT'' OR svc.service_abbrev = ''ONCONEXT'' OR svc.service_abbrev = ''ONCONPLUS'' OR svc.service_abbrev = ''ONCOTRACE'' OR svc.service_abbrev = ''ONCOTRAIL'' OR svc.service_abbrev = ''ONCOCOUNT''
        OR svc.service_abbrev = ''IMMUNOSTAT'' OR svc.service_abbrev = ''NAGALASE'' OR svc.service_abbrev = ''PRIMESPOT'' OR svc.service_abbrev = ''CHEMOSNIP''
    GROUP BY ' + ' ' + @GroupBy + '
    ORDER BY rqs.service_id ASC'

    EXEC(@SQL)

Answer 1

The problem is that you've defined your stored procedure parameters as just varchar - without specifying a length.

This does NOT specify a varchar of whatever length is needed - the default length is 1 character - so if you pass in any string that starts with an R........, it will be truncated to just R .....

So the strong recommendation is: ALWAYS define an explicit length for your varchar parameters and/or variables in T-SQL!

And also: if you pass in dates - the recommendation is to use the most appropriate datatype - not varchar in this case, but rather DATE or DATETIME2(n) .....

So try this :

ALTER PROCEDURE [dbo].[aslGetCommonv1]
    @StartDate DATE,
    @EndDate DATE,
    @GroupBy VARCHAR(100)

and I would also strongly recommend to define that length in the VB.NET code where you call this stored procedure:

cmd.Parameters.Add("@GroupBy", SqlDbType.VarChar, 100).Value = CountDoc
                                                ^^^^^^