Incorrect Syntax issue with SQL Query

[dougancil]

I have the following code:

Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles startpayrollButton.Click
        Dim ssql As String = "select MAX(payrolldate) AS [payrolldate], " & _
                 "dateadd(dd, ((datediff(dd, '17530107', MAX(payrolldate))/7)*7)+7, '17530107') AS [Sunday]" & _
                  "from dbo.payroll" & _
                  " where payrollran = 'no'"
        Dim oCmd As System.Data.SqlClient.SqlCommand
        Dim oDr As System.Data.SqlClient.SqlDataReader
 
        oCmd = New System.Data.SqlClient.SqlCommand
        Try
            With oCmd
                .Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx)
                .Connection.Open()
                .CommandType = CommandType.Text
                .CommandText = ssql
                oDr = .ExecuteReader()
            End With
            If oDr.Read Then
                payPeriodStartDate = oDr.GetDateTime(1)
                payPeriodEndDate = payPeriodStartDate.AddDays(7)
                Dim ButtonDialogResult As DialogResult
                ButtonDialogResult = MessageBox.Show("      The Next Payroll Start Date is: " & payPeriodStartDate.ToString() & System.Environment.NewLine & "            Through End Date: " & payPeriodEndDate.ToString())
                If ButtonDialogResult = Windows.Forms.DialogResult.OK Then
 
                    exceptionsButton.Enabled = True
                    startpayrollButton.Enabled = False
 
                End If
            End If
            oDr.Close()
            oCmd.Connection.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd.Connection.Close()
        End Try
 
    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exceptionsButton.Click
        Dim sql As String = "SELECT [Exceptions].Employeenumber,[Exceptions].exceptiondate, [Exceptions].starttime, [exceptions].endtime, [Exceptions].code, datediff(minute, starttime, endtime)  as duration INTO scratchpad3" & _
          "FROM Employees INNER JOIN Exceptions ON [Exceptions].EmployeeNumber = [Exceptions].Employeenumber" & _
          "where [Exceptions].exceptiondate between @payperiodstartdate and @payperiodenddate" & _
          "GROUP BY [Exceptions].Employeenumber, [Exceptions].Exceptiondate, [Exceptions].starttime, [exceptions].endtime," & _
          "[Exceptions].code, [Exceptions].exceptiondate"
        Dim oCmd As System.Data.SqlClient.SqlCommand
        Dim oDr As System.Data.SqlClient.SqlDataReader
        oCmd = New System.Data.SqlClient.SqlCommand
        Dim myCommand As New SqlClient.SqlCommand()
        Try
            With oCmd
                .Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx")
                .Connection.Open()
                .CommandType = CommandType.Text
                .CommandText = sql
                oDr = .ExecuteReader()
                myCommand.Parameters.AddWithValue("@payperiodstartdate", payPeriodStartDate)
                myCommand.Parameters.AddWithValue("@payperiodenddate", payPeriodEndDate)
                myCommand.CommandText = "dbo.sp_opsum"
            End With
            oDr.Close()
            oCmd.Connection.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd.Connection.Close()
        End Try
        Exceptions.Show()
    End Sub
 

and when I run the program I get the error:

"Line1: Incorrect syntax near 'Employees'"

Which is part of my second SQL query. I can debug up to that point without my application breaking. I have gone back to my SQL management studio and ran (and re-ran) my sql query and it works fine. Can someone please help me as to why when I run a working query in VB, it throws this error?

Thank you

Doug

[yarbrough40]

take a look at your INNER JOIN. you have

INNER JOIN Exceptions ON [Exceptions].EmployeeNumber = [Exceptions].Employeenumber  

you may want to correct this. But I think your issue is what Rabbit is saying...

[Rabbit]

strVar = "Hello" & _
     "World"

The results is HelloWorld.

[dougancil]

Rabbit,

So then how I have my query formatted is incorrect?

[yarbrough40]

is this any clearer?

 strVar = "Hi" & _ 
     "my" & _ 
     "name" & _ 
     "is" & _ 
     "slim" & _ 
     "shady" & _ 

The results is Himynameisslims hady

[Rabbit]

Yes, put a space at the end or beginning of the lines.

strVar = "Hello" & _ 
     " World"

Result is "Hello World"

[dougancil]

Thanks,

that's what it was.