asked on

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

Meir Rivkin

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

Murray Brown

ASKER

Hi. The code ran fine but I can't see the View in the database

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Murray Brown

ASKER

Thanks. I decided against using a view 10 minutes ago, so we are on the same page.

VB.net Create View Must declare the scalar variable &quot;@sdate&quot;.

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