Problem with script component syntax

Question

I am looking for some help with the syntax within a script component I am using in my data flow. I am getting en error msg saying " Line 1: Incorrect syntax near '7'."

Can anyone help me with this issue?

Thanks in advance.


Code Below:

Imports System
Imports System.Collections
Imports System.Data
Imports System.Data.OleDb
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent

    Dim IsBusDay As Boolean = True

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        Dim c As New OleDbConnection(Me.Connections.DGLookUp.ConnectionString)
        Dim next_bus_day As DateTime

        While (IsBusDay = True)
            Dim sqlCommand As New OleDbCommand("SELECT [bhp_date] FROM [Lookup].[dbo].[BHPLookup] where bhp_date = " & Row.nextbusday, c)
            c.Open()
            Dim sqlResult As OleDbDataReader

            sqlResult = sqlCommand.ExecuteReader()
            If sqlResult.HasRows = True Then

                Row.nextbusday = DateAdd(DateInterval.Day, 1, Row.nextbusday)
            Else
                IsBusDay = False
            End If
        End While

    End Sub

End Class

Answer 1

i dont have an Idea but i have few question ?

1- Are you tracing it to see where is it failling?
2-in .... Dim sqlCommand As New OleDbCommand("SELECT [bhp_date] FROM [Lookup].[dbo].[BHPLookup] where bhp_date = " & Row.nextbusday, c ) 
in the section ...... where bhp_date = " & Row.nextbusday, c ) ..... the Row.nextbusday should be in sigle cote? i am just asking lik e

.. where bhp_date = '" & Row.nextbusday & "'", c ) 
                              |                                   |
                              |                                   |
                              |                                   |
                        Single cote                     Single cote


I am just asking , not sure if i am right.

---

Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)

Answer 2

I think Nik3 is right. in your code, Row.nextbusday will just execute the ToString function on a DateTime object this will result in a SQL query like:

SELECT [bhp_date] FROM [Lookup].[dbo].[BHPLookup] where bhp_date = 21 Jan 2009 12:00:00

Or some other format depending on the regional settings of the execution location. Instead of doing this you should use one of the following options:

1. Parametrize your query and use proper ADO.NET parameter object to pass in the value
2. Construct a string in the format YYYYMMDD wrapped in single quotes e.g. 
   
   

   string.Format("SELECT [bhp_date] FROM [Lookup].[dbo].[BHPLookup] where bhp_date = '{0:yyyyMMdd}'", Row.nextbusday)
   

See this link for .NET format strings http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx

---

Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)