Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VB.net Create SQL View with two parameters

Posted on 2013-06-24
4
Medium Priority
?
470 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:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 22

Accepted Solution

by:
Om Prakash earned 2000 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:Murray Brown
ID: 39270576
Thanks very much
0
 
LVL 49

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 49

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

715 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