Setting User Inputed Date Range Inside A MS SQL Query from VB.NET

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)
    End Sub

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?

charlesboyerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vikas413Commented:
define your variables at global/class level so they are available in different functions if your quresy is not in same function i.e. 'butSearch_Click'
then

use following

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 '"& Astart.toString("yyyy-MM-dd")  1 & "' AND '" & Alast .toString("yyyy-MM-dd") & "')"
charlesboyerAuthor Commented:
Thanks, vikas413.

I started thinking it through last night and came up with this:

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 Request_Date Between @Start and @End)"

and fixed the WHERE statement by adding this:

<SelectParameters>
            <asp:ControlParameter ControlID="txtStart" Name="Start" PropertyName="Text" Type="DateTime"/>
            <asp:ControlParameter ControlID="txtEnd" Name="End" PropertyName="Text" Type="DateTime" />
 </SelectParameters>

With the text boxes actually free to use a mm/dd/yyyy format now.

Works like a champ, but your code made me think things through much better than I was before.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vikas413Commented:
my solution will also work .. then why it is not awarded as answer?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.