To add to this a bit, I've a suggestion about how to deal with long strings (which you might already know). If I were to put the above sql into code I'd use this technique:
Dim sqlBuilder As New System.Text.StringBuilder(255)
With sqlBuilder
.AppendLine("SELECT DISTINCT dbo.SC_Appliances.*")
.AppendFormat("FROM {0}", Me.TableName).Append(ControlChars.NewLine)
.AppendLine(" RIGHT OUTER JOIN dbo.ServiceCalls")
.AppendLine(" ON (dbo.SC_Appliances.FK_ServiceCalls = dbo.ServiceCalls.PK_ServiceCalls)")
'-- Convert enums to integers when using AppendFormat, or you'll get the enum value's name
.AppendFormat("WHERE (dbo.ServiceCalls.Status NOT IN ({0},{1}))", CInt(ServiceCallStatus.Done), CInt(ServiceCallStatus.Void)).Append(ControlChars.NewLine)
.AppendLine(" AND (dbo.SC_Appliances.ServicedDate = @pServiceCallsScheduleDate);")
End With
'-- If you debug here, the SQL string will be nicely formatted.
cmd.CommandText = sqlBuilder.ToString()
This isn't really suggesting that you put long SQL statements in code (Trent's suggestion is a good one). Rather just a suggestion about using StringBuilder instead of the concatenation operator. Each of the strings concatenated becomes an immutable variable, so StringBuilder is easier on memory (only one string created when you call ToString). Also, note that you can use format strings via AppendFormat. Note that using AppendFormat does a ToString on any of the replacement values, thus when the value is an enum, you need to convert to an integer first, other wise you'll get the name of the enum (resulting in SQL like 'NOT IN (Done, Void)' instead of 'NOT IN (2, 3)'). Finally, if you put a break in code after its been built, and look at the result, it will be nicely formatted if you need to debug it.
Again, I'm not suggesting you put long SQL into your code. I've just really come to appreciate this technique of handling long strings.
