Let me start by saying I am new to .NET and MS SQL, but have been tasked with generating a report from a table that contains server tracking info for my workgroup. Basically, the intent is to generate reports of virtual machine activity for management.
The report generation page in question issues a query to an MS SQL DB, and on that page I have two text boxes where the user can input dates. Those boxes are validated to the form YYYY-MM-DD, so I know I have the required data in the right form. I also convert the user input to DateTime with this Sub when the "submit" button is clicked to actuate the query"
Protected Sub butSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles butSearch.Click
Dim Astart As DateTime
Dim Alast As DateTime
Astart = CDate(txtStart.Text)
Alast = CDate(txtEnd.Text)
My query is complex:
SelectCommand="SELECT tblNewServer.VMApproved, tblNewServer.VMDenied, tblNewServer.idSupporter_NewServer, tblNewServer.DISKgb, tblNewServer.RAMgb,
tblNewServer.Replace, tblNewServer.CPUmhz, tblNewServer.Request_Date, tblNewServer.Server_Name, tblNewServer.New_Server_ID,
tblNewServer.idManufacturer_NewServer, vwWSTSSupporters.Supporter_ID AS Expr1, vwWSTSSupporters.Admin AS Expr2
FROM tblNewServer INNER JOIN
vwWSTSSupporters ON tblNewServer.idSupporter_NewServer = vwWSTSSupporters.Supporter_ID
WHERE (tblNewServer.idManufacturer_NewServer = '20' AND tblNewServer.Request_Date BETWEEN '2010-03-01' AND '2010-05-01')"
This works and returns the records between March 1 and May 1 as stored in tblNewServer.Request_Date.
What I need to do is to substitute those hard coded dates in the WHERE statement to have the values of Astart and Alast.
I have tried several different methods to set these variables into the WHERE portion of the SELECT statement, but so far nothing has worked.
Do I need to use a SET Statement inside the Query and if so
a) where would be the syntax-correct place inside this somewhat complex query
b) for the BETWEEN statement in the WHERE, how would I place those variables such that the query would execute?