I have the following code which works, but it may be hard to either debug or understand at a later time:
Dim cmd As New SqlCommand()cmd.CommandText = "SELECT DISTINCT dbo.SC_Appliances.* " & _"FROM dbo.SC_Appliances " & _"RIGHT OUTER JOIN dbo.ServiceCalls ON (dbo.SC_Appliances.FK_ServiceCalls = dbo.ServiceCalls.PK_ServiceCalls) " & _"WHERE " & _"(dbo.ServiceCalls.Status NOT IN (" & ServiceCallStatus.Done & "," & ServiceCallStatus.Void & ")) AND " & _"(dbo.SC_Appliances.ServicedDate = @pServiceCallsScheduleDate);"cmd.Parameters.AddWithValue("@pServiceCallsScheduleDate", serviceCallsScheduleDate).SqlDbType = SqlDbType.DateTimeReturn cmd
Dim
cmd.CommandText =
cmd.Parameters.AddWithValue(
Basically that will translate to this:
SELECT DISTINCT dbo.SC_Appliances.* FROM dbo.SC_Appliances RIGHT OUTER JOIN dbo.ServiceCalls ON (dbo.SC_Appliances.FK_ServiceCalls = dbo.ServiceCalls.PK_ServiceCalls) WHERE (dbo.ServiceCalls.Status NOT IN (2,3)) AND (dbo.SC_Appliances.ServicedDate = @pServiceCallsScheduleDate);
FROM dbo.SC_Appliances
RIGHT OUTER JOIN dbo.ServiceCalls ON (dbo.SC_Appliances.FK_ServiceCalls = dbo.ServiceCalls.PK_ServiceCalls)
WHERE
(dbo.ServiceCalls.Status NOT IN (2,3)) AND
(dbo.SC_Appliances.ServicedDate = @pServiceCallsScheduleDate);