• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 531
  • Last Modified:

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

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
Murray Brown
Asked:
Murray Brown
  • 2
1 Solution
 
Meir RivkinFull stack Software EngineerCommented:
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
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi. The code ran fine but I can't see the View in the database
0
 
PortletPaulCommented:
>>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
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks. I decided against using a view 10 minutes ago, so we are on the same page.
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now