VB.net Problem creating SQL Views with parameters

Hi

I am trying to create a view with two parameters in the following code
but get the error:
SqlDbTypes is not declared
Baz is not declared

What do I need to alter to make this query work? Thanks


    Sub oCreate_View()

        Dim sSQL As String
        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"


        Dim connection As New SqlConnection(My.Settings.CS_Setting)
        Dim cmd As New SqlCommand(sSQL, connection)
        cmd.Parameters.Add("@sdate", SqlDbTypes.VarChar, 50).Value = Baz
        cmd.Parameters.Add("@edate", SqlDbTypes.VarChar, 50).Value = Baz

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

    End Sub
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
 
Meir RivkinConnect With a Mentor Full stack Software EngineerCommented:
instead of Baz, u need to put the start and end date values.
0
 
Meir RivkinFull stack Software EngineerCommented:
import the System.Data namespace
and use:
    Sub oCreate_View()

        Dim sSQL As String
        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"


        Dim connection As New SqlConnection(My.Settings.CS_Setting)
        Dim cmd As New SqlCommand(sSQL, connection)
        cmd.Parameters.Add("@sdate", System.Data.SqlDbType.VarChar, 50).Value = Baz
        cmd.Parameters.Add("@edate", System.Data.SqlDbType.VarChar, 50).Value = Baz

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

    End Sub

Open in new window


in regards to Baz, did u forgot to declare it?
u set it as value to the sql parameters, where did it come from?
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Sorry. I am confused. I though that this code was to just create the View with parameters.
I don't know why Baz is there
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks
0
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.

All Courses

From novice to tech pro — start learning today.