Solved

VB.net Problem creating SQL Views with parameters

Posted on 2013-06-24
4
312 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:murbro
  • 2
  • 2
4 Comments
 
LVL 42

Expert Comment

by:sedgwick
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:murbro
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:
sedgwick earned 500 total points
ID: 39270728
instead of Baz, u need to put the start and end date values.
0
 

Author Closing Comment

by:murbro
ID: 39270733
Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

679 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