Solved

VB.net Create SQL View with two parameters

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

839 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