Solved

VB.net Problem creating SQL Views with parameters

Posted on 2013-06-24
4
309 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB.Net - For Loop Error 5 37
Get String split 5 48
Advice on Xojo as a development tool over VB. 4 39
Get size of each directory on each mapped drive 5 21
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now