Solved

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

Posted on 2013-06-24
4
477 Views
Last Modified: 2013-06-24
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
0
Comment
Question by:murbro
  • 2
4 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 39270735
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

0
 

Author Comment

by:murbro
ID: 39270752
Hi. The code ran fine but I can't see the View in the database
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39270852
>>I can't see the View in the database
?

If you are trying to create a view in the database the syntax id different (it needs more), you are currently only running a  SQL query.

Why do you believe a view is required?
0
 

Author Closing Comment

by:murbro
ID: 39270861
Thanks. I decided against using a view 10 minutes ago, so we are on the same page.
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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
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…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

861 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

29 Experts available now in Live!

Get 1:1 Help Now