Solved

VB.net Create SQL View with two parameters

Posted on 2013-06-24
4
450 Views
Last Modified: 2013-06-24
Sub Create_View_Work_Totals()
 
  Hi

I am using the following VB.net code to create a View in a SQL table.
I want to replace '12/12/2012' And '12/12/2013' with a parameter so that the view can be called using any date range. How do I change this code? Also when I call the query, how would I pass in the two parameters? Thanks


   Dim oViewName, sSQL As String
     oViewName = "[Work_Totals]"
     DeleteView (oViewName)

     sSQL = "SELECT Work_Hours.EmployeeID, Work_Hours.Hours, Work_Hours.Status"
     sSQL = sSQL & " FROM Work_Hours"
     sSQL = sSQL & " WHERE Work_Hours.Date > '12/12/2012' And Work_Hours.Date < '12/12/2013'"
     sSQL = sSQL & " GROUP BY Work_Hours.EmployeeID, Work_Hours.Status"
 
    Call oCreate_View(oViewName, sSQL)
     
 End Sub
0
Comment
Question by:murbro
  • 2
4 Comments
 
LVL 22

Accepted Solution

by:
Om Prakash earned 500 total points
ID: 39270572
you can add parameters like:
sSQL = "SELECT Work_Hours.EmployeeID, Work_Hours.Hours, Work_Hours.Status"
sSQL = sSQL & " FROM Work_Hours"
sSQL = sSQL & " WHERE Work_Hours.Date > @sdate And Work_Hours.Date < @edate"
sSQL = sSQL & " GROUP BY Work_Hours.EmployeeID, Work_Hours.Status"

SqlCommand cmd = new SqlCommand(sSQL, yourSqlConnection)
cmd.Parameters.Add("@sdate", SqlDbTypes.VarChar, 50).Value = Baz
cmd.Parameters.Add("@edate", SqlDbTypes.VarChar, 50).Value = Baz
'execute the command

Open in new window

0
 

Author Closing Comment

by:murbro
ID: 39270576
Thanks very much
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39270593
mmmm, you had me worried when you mentioned "view"

1. a "view" is a specific object type in the database, and these don't accept parameters.
the code above is executing a "query"

2. you really should be using YYYY-MM-DD string sequence (with hyphens), this is far safer and unambiguous

3. > '12/12/2012' and < '12/12/2013'
to get a full year of data please include the equal operator on the lower boundary
>= '2012-12-12' and < '2013-12-12'
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39270612
@murbro

please do take care with the method you use to select information by date range.
for more on this topic please see: "Beware of Between"
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now