Improve company productivity with a Business Account.Sign Up

x
?
Solved

VB.net Create SQL View with two parameters

Posted on 2013-06-24
4
Medium Priority
?
484 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
  • 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 50

Expert Comment

by:Paul
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 50

Expert Comment

by:Paul
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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…
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

601 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