Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VB.net Problem creating SQL Views with parameters

Posted on 2013-06-24
4
Medium Priority
?
317 Views
Last Modified: 2013-06-24
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
0
Comment
Question by:Murray Brown
  • 2
  • 2
4 Comments
 
LVL 42

Expert Comment

by:Meir Rivkin
ID: 39270615
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
 

Author Comment

by:Murray Brown
ID: 39270632
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
 
LVL 42

Accepted Solution

by:
Meir Rivkin earned 2000 total points
ID: 39270728
instead of Baz, u need to put the start and end date values.
0
 

Author Closing Comment

by:Murray Brown
ID: 39270733
Thanks
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Loops Section Overview
Suggested Courses

877 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