Solved

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

Posted on 2013-06-24
4
487 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:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:Murray Brown
ID: 39270752
Hi. The code ran fine but I can't see the View in the database
0
 
LVL 49

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

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

688 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