to put a variable in a Sql statement more clever way..

[legendgod]

Hello friends. Thank you for your suggestion in my previous questions.

In order to query some result out of DB inside a time period, I typed this code:

Dim strSQL As String = "Select * From ScheduleView where schStartTime
between '"
      strSQL = strSQL & StartDay
      strSQL = strSQL & "' and '"
      strSQL = strSQL & EndDay
      strSQL = strSQL & "' order by hId,schStartTime ; Select * From
HomeHelper order by hId"

which "StartDay" & "EndDay" is 2 DateTime variable. Although it works well for my program... I think there must be some more clever way to do it out. If the way do exist please give me some idea. Thank you.

[PlausiblyDamp]

What database are you using? If it is a proper client/server db like SQL or Oracle then you could create a server side stored procedure and use that rather than the string concatenation you are currently using.

[legendgod]

I am using SQL server. I will try your suggestion, thank you.

[JABE]

If the string concatenation code is really your concern (rather than db programming efficiency w/c has been addressed by Plaus' reply), using String.Format may make your code look neater:

 

Dim strSQL As String = String.Format("Select * From ScheduleView where schStartTime between '{0}' and '{1}' order by etc...", StartDay, EndDay)

[PlausiblyDamp]

Personally I'd always opt for stored procedures whenever the DB provides the capability. String concatenation (or its variants like String.Format) leave you open to potential security flaws and can increase the amount of validation you need to perform (search for phrases like SQL Injection to see what I mean).

[pelikan]

I may be wrong, but why use strings?

 

SELECT * FROM ScheduleView WHERE

DateDiff( day, StartDate, schStartTime ) > 0 AND DateDiff( day, schStartTime, EndDate ) > 0

ORDER BY hID, schStartTime

[Cassio]

I always use parameters. I think its safer. If your your program runs on a machine that has a diferent culture the date format may be diferent. Using parameters avoid this kind of problem.