Solved

VB.net Create SQL View with two parameters

Posted on 2013-06-24
4
454 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

776 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