Solved

VB.net Create View Must declare the scalar variable "@sdate".

Posted on 2013-06-24
4
480 Views
Last Modified: 2013-06-24
Hi

I am getting the error "Must declare the scalar variable "@sdate"

when I run the following code to create a SQL View

    Public Sub oCreate_View1()

        Dim sSQL As String
        sSQL = "SELECT Work_Hours.EmployeeID, Sum(Work_Hours.Hours) As Total_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"

        Dim strViewName As String = "Total_Hours"

        Dim S As String = "CREATE VIEW " & strViewName & " AS " & " " & sSQL

        Dim connection As New SqlConnection(My.Settings.CS_Setting)
        Dim cmd As New SqlCommand(sSQL, connection)


        connection.Open()
        cmd.ExecuteNonQuery()
        connection.Close()

    End Sub
0
Comment
Question by:murbro
  • 2
4 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 39270735
u need to provide the start/end date parameters if u use the sql like u did.
also u need to change the sqlDataType to System.Data.SqlDbType.DateTime:
 Public Sub oCreate_View1()

        Dim sSQL As String
        sSQL = "SELECT Work_Hours.EmployeeID, Sum(Work_Hours.Hours) As Total_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"

        Dim strViewName As String = "Total_Hours"

        Dim S As String = "CREATE VIEW " & strViewName & " AS " & " " & sSQL

        Dim connection As New SqlConnection(My.Settings.CS_Setting)
        Dim cmd As New SqlCommand(sSQL, connection)

dim startdate as DateTime = DateTime.Parse("1\1\2013")
dim enddate as DateTime = DateTime.Parse("1\1\2014")
        cmd.Parameters.Add("@sdate", System.Data.SqlDbType.DateTime).Value = startdate 
        cmd.Parameters.Add("@edate", System.Data.SqlDbType.DateTime).Value = enddate 


        connection.Open()
        cmd.ExecuteNonQuery()
        connection.Close()

    End Sub

Open in new window

0
 

Author Comment

by:murbro
ID: 39270752
Hi. The code ran fine but I can't see the View in the database
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39270852
>>I can't see the View in the database
?

If you are trying to create a view in the database the syntax id different (it needs more), you are currently only running a  SQL query.

Why do you believe a view is required?
0
 

Author Closing Comment

by:murbro
ID: 39270861
Thanks. I decided against using a view 10 minutes ago, so we are on the same page.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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