Passing Date Parameters

[Bill Humphrey]

Hi

I'm a bit of a rooky at vb .net and am trying to build up a where caluse to pass two date parameters 'StartDate' and 'EndDate' . I have priority working but I'm not sure of how to add the two dates in the where clause:

Any suggestions appreciated

Public Function GetCategoryPerformance(ByVal PriorityType As String) As DataSet

Dim cn As New SqlConnection("data source=CADTRAINING;persist security info=True;initial catalog=pathwaysreporting;user id=cad;password=cad;")

Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * from sp_category_performance where where priority = '" & PriorityType & "'", cn)

Dim ds As DataSet = New DataSet

Try
da.Fill(ds, "CategoryPerformance")
Catch ex As Exception
Throw New Exception(ex.Message)

End Try

Return ds

End Function

End Class

 

[jmcilhinney]

One of the reasons you are having trouble is that you're using string concatenation to build your SQL string. That's a bad idea all round. Use parameters and using dates is no different to using any other data, e.g.

Dim da As New SqlDataAdapter("SELECT * FROM sp_category_performance WHERE priority = @priority AND [date] BETWEEN @startdate AND @enddate", cn)

With da.SelectCommand.Parameters
    .AddWithValue("@priority", PriorityType)
    .AddWithValue("@startdate", Me.startDatePicker.Value.Date)
    .AddWithValue("@enddate", Me.endDatePicker.Value.Date)
End With

 

[jmcilhinney]

Also, from your query it looks like you have a table named "sp_category_performance". Surely that would be a name for a Stored Procedure, not a table.