Link to home
Create AccountLog in
Avatar of thenthorn1010
thenthorn1010Flag for United States of America

asked on

How to Read In Sample SQL Data For Visual Basic 2010 class With Error

I am attempting to test a data reader in my Visual Basic 2010 class that I have attached with the sample test code, and I am getting the following error when I run the code through the debugger. Could someone help me figure out where I am going wrong in my code? I cannot seem to find the issue for this code to fail when test SQL code is passed to it when it does work with a regular database interaction to generate file output.

Test 'TrulinX.Test.SOTest.EDIAckOutboundProcessFixture.Test' failed: Csla.DataPortalException : DataPortal.Fetch failed (System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at TrulinX.BusinessObjects.SO.EDIAckOutboundFileProcess.DataPortal_Fetch(Criteria criteria) in C:\Users\tlh\Documents\TrulinXDevTrunk\TrulinXMasterSolution\TrulinX.BusinessObjects\SO\EDIAckOutboundFileProcess.vb:line 191)
  ----> Csla.Server.CallMethodException : DataPortal_Fetch method call failed
  ----> System.Data.SqlClient.SqlException : Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
Incorrect syntax near the keyword 'Select'.
      at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
      at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
      at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
      at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
      at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
      at System.Data.SqlClient.SqlDataReader.get_MetaData()
      at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
      at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
      at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
      at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
      at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
      at System.Data.SqlClient.SqlCommand.ExecuteReader()
      SO\EDIAckOutboundFileProcess.vb(191,0): at TrulinX.BusinessObjects.SO.EDIAckOutboundFileProcess.DataPortal_Fetch(Criteria criteria)
      at Csla.MethodCaller.CallMethod(Object obj, MethodInfo info, Object[] parameters)
      at Csla.Server.SimpleDataPortal.Fetch(Type objectType, Object criteria, DataPortalContext context)
      at Csla.DataPortal.Fetch(Type objectType, Object criteria)
      at Csla.DataPortal.Fetch[T](Object criteria)
      SO\EDIAckOutboundFileProcess.vb(22,0): at TrulinX.BusinessObjects.SO.EDIAckOutboundFileProcess.GetProcess(String SONumber)
      SO\EDIAckOutboundProcessFixture.vb(6,0): at TrulinX.Test.SOTest.EDIAckOutboundProcessFixture.Test()
      --CallMethodException
      at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
      at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
      at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
      at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
      at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
      at System.Data.SqlClient.SqlDataReader.get_MetaData()
      at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
      at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
      at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
      at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
      at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
      at System.Data.SqlClient.SqlCommand.ExecuteReader()
      SO\EDIAckOutboundFileProcess.vb(191,0): at TrulinX.BusinessObjects.SO.EDIAckOutboundFileProcess.DataPortal_Fetch(Criteria criteria)
      at Csla.MethodCaller.CallMethod(Object obj, MethodInfo info, Object[] parameters)
      at Csla.Server.SimpleDataPortal.Fetch(Type objectType, Object criteria, DataPortalContext context)
      --SqlException
      at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
      at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
      at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
      at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
      at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
      at System.Data.SqlClient.SqlDataReader.get_MetaData()
      at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
      at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
      at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
      at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
      at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
      at System.Data.SqlClient.SqlCommand.ExecuteReader()
      SO\EDIAckOutboundFileProcess.vb(191,0): at TrulinX.BusinessObjects.SO.EDIAckOutboundFileProcess.DataPortal_Fetch(Criteria criteria)

0 passed, 1 failed, 0 skipped, took 28.70 seconds (NUnit 2.5.5).


Imports System.Text
Namespace SO



    <Serializable()> Public Class EDIAckOutboundFileProcess : Inherits ReadOnlyBase(Of EDIAckOutboundFileProcess)

        Private mErrorMessage As String = ""
        Private mSONumber As String

        Private Sub New()
            ' Require use of factory methods.
        End Sub

        Public ReadOnly Property ErrorMessage() As String
            Get
                Return mErrorMessage
            End Get
        End Property

        Public Shared Function GetProcess(ByVal SONumber As String) As EDIAckOutboundFileProcess
            Return DataPortal.Fetch(Of EDIAckOutboundFileProcess)(New Criteria(SONumber))
        End Function

        <Serializable()> Private Class Criteria
            Public ReadOnly SONumber As String
            Public Sub New(ByVal SONumber As String)
                Me.SONumber = SONumber
            End Sub
        End Class

        Private Overloads Sub DataPortal_Fetch(ByVal criteria As Criteria)
            Dim mHeaderOutput As New StringBuilder("")
            Dim mDetailOutput As New StringBuilder("")

            'Test the data that is going to be written out to the EDIAckTestFile By Feeding it Data...TLH 3/28/2011
            'Write Data to the header portion of the outbound file
            mHeaderOutput.Append("Select '3M' As Customer, ")
            mHeaderOutput.Append("'12345678' As SONumber, ")
            mHeaderOutput.Append("'110301' As DateOrdered, ")
            mHeaderOutput.Append("'UPS' As ShipMethod, ")
            mHeaderOutput.Append("'129346789' As CustomerPO, ")
            mHeaderOutput.Append("'129346789012' As SenderID, ")
            mHeaderOutput.Append("'5247151415641444448942021' As ReleaseNumber, ")
            mHeaderOutput.Append("'110302' As PODate, ")
            mHeaderOutput.Append("'Amazon' As SupplierName, ")
            mHeaderOutput.Append("'5217 Ginger Pwky' As SupplierAddressLine1, ")
            mHeaderOutput.Append("'Suite 212' As SupplierAddressLine2, ")
            mHeaderOutput.Append("'Chicago' As SupplierCity, ")
            mHeaderOutput.Append("'IL' As SupplierStateOrProvince, ")
            mHeaderOutput.Append("'USA' As SupplierCountry, ")
            mHeaderOutput.Append("'Lexington Services' As ShippedFromName, ")
            mHeaderOutput.Append("'12817 Wall Street' As ShippedFromAddressLine1, ")
            mHeaderOutput.Append("'Room 1234' As ShippedFromAddressLine2, ")
            mHeaderOutput.Append("'Lexington' As ShippedFromCity, ")
            mHeaderOutput.Append("'KY' As ShippedFromStateOrProvince, ")
            mHeaderOutput.Append("'81475' As ShippedFromPostalCode, ")
            mHeaderOutput.Append("'USA' As ShippedFromCountry, ")
            mHeaderOutput.Append("'Billy Bob Thorton' As BilledToName, ")
            mHeaderOutput.Append("'852 Wilshire Lane' As BilledToAddressLine1, ")
            mHeaderOutput.Append("'Apartment 3D' As BilledToAddressLine2, ")
            mHeaderOutput.Append("'Beverley Hills' As BilledToCity, ")
            mHeaderOutput.Append("'CA' As BilledToStateOrProvince, ")
            mHeaderOutput.Append("'Waste Enterprises' As ShippedToName, ")
            mHeaderOutput.Append("'1234 Georgetown Road' As ShippedToAddressLine1, ")
            mHeaderOutput.Append("'Complex 2B' As ShippedToAddressLine2, ")
            mHeaderOutput.Append("'Hudson' As ShippedToCity, ")
            mHeaderOutput.Append("'OH' As ShippedToStateOrProvince, ")
            mHeaderOutput.Append("'USA' As ShippedToCountry, ")
            mHeaderOutput.Append("'8080834561' As GSSenderID ")


            'Write Data to the detail portion of the outbound file
            mDetailOutput.Append("Select '0010' As LineNumber, ")
            mDetailOutput.Append("Select '12' As OrderQuantity, ")
            mDetailOutput.Append("Select 'EA' As UnitOfMeasure, ")
            mDetailOutput.Append("Select '351.32' As UnitPriceCalculated, ")
            mDetailOutput.Append("Select '3.52' As DiscountMethodCalculated, ")
            mDetailOutput.Append("Select '0.23' As DiscountValueCalculated, ")
            mDetailOutput.Append("Select '212.39' As NetUnitPrice, ")
            mDetailOutput.Append("Select '355-G' As ItemNumber, ")
            mDetailOutput.Append("Select 'Hose' As ItemDescription, ")
            mDetailOutput.Append("Select '00004571' As CustomerPOLinenumber, ")
            mDetailOutput.Append("Select '11' As POOrderQty, ")
            mDetailOutput.Append("Select 'EA' As POUnitofMeasure, ")
            mDetailOutput.Append("Select '212.39' As POUnitPrice, ")
            mDetailOutput.Append("Select '355-G' As CustomerPartNumber, ")
            mDetailOutput.Append("Select 'Green Thing' As Podescription, ")
            mDetailOutput.Append("Select '110401' As PORequiredDate, ")
            mDetailOutput.Append("Select '110402' As POEstimatedShipDate, ")
            mDetailOutput.Append("Select '110403' As EstimatedDeliveryDate, ")
            mDetailOutput.Append("Select '10' As QuantityShipped, ")
            mDetailOutput.Append("Select '11' As QuantityReleased ")
            mDetailOutput.Append("UNION ALL ")
            mDetailOutput.Append("Select '0020' As LineNumber, ")
            mDetailOutput.Append("Select '2' As OrderQuantity, ")
            mDetailOutput.Append("Select 'EA' As UnitOfMeasure, ")
            mDetailOutput.Append("Select '312.32' As UnitPriceCalculated, ")
            mDetailOutput.Append("Select '13.52' As DiscountMethodCalculated, ")
            mDetailOutput.Append("Select '0.28' As DiscountValueCalculated, ")
            mDetailOutput.Append("Select '212.39' As NetUnitPrice, ")
            mDetailOutput.Append("Select '422-G' As ItemNumber, ")
            mDetailOutput.Append("Select 'Hose' As ItemDescription, ")
            mDetailOutput.Append("Select '00004572' As CustomerPOLinenumber, ")
            mDetailOutput.Append("Select '2' As POOrderQty, ")
            mDetailOutput.Append("Select 'EA' As POUnitofMeasure, ")
            mDetailOutput.Append("Select '312.32' As POUnitPrice, ")
            mDetailOutput.Append("Select '422-G' As CustomerPartNumber, ")
            mDetailOutput.Append("Select 'Red Round Thing' As Podescription, ")
            mDetailOutput.Append("Select '110401' As PORequiredDate, ")
            mDetailOutput.Append("Select '110402' As POEstimatedShipDate, ")
            mDetailOutput.Append("Select '110403' As EstimatedDeliveryDate, ")
            mDetailOutput.Append("Select '2' As QuantityShipped, ")
            mDetailOutput.Append("Select '2' As QuantityReleased ")

            mSONumber = criteria.SONumber
            Using cn As SqlConnection = OpenDBConnection()
                Using cmd As SqlCommand = cn.CreateCommand
                    cmd.CommandType = CommandType.Text

                    'Test the header output with the data that was entered into the header information fields from above
                    cmd.CommandText = mHeaderOutput.ToString & mDetailOutput.ToString


                    Dim oWrite As System.IO.StreamWriter
                    Using dr As New SafeDataReader(cmd.ExecuteReader)
                        'Read the header result
                        If dr.Read Then 'From Debugging the code, the error is occurring in this spot.
                            Dim header As New EDIAckHeader(dr)
                            oWrite = IO.File.CreateText("C:\EDIACKOUTPUT.txt")

                            'Write Out the Header Rows of the EDI Outbound text file
                            oWrite.WriteLine(header.ToString)

                            dr.NextResult()
                            Do
                                Do While dr.Read
                                    Dim lines As New EDIAckHeaderDetail(dr)
                                    oWrite.WriteLine(lines.ToString)
                                Loop
                            Loop While dr.NextResult()

                            oWrite.Close()
                        Else
                            'Nothing to do
                            Return
                        End If

                    End Using
                End Using
            End Using

        End Sub

        Protected Overrides Function GetIdValue() As Object
            Return mSONumber
        End Function
    End Class
End Namespace

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of thenthorn1010
thenthorn1010
Flag of United States of America image

ASKER

Thanks for the help...this was something I missed when constructing the SQL statements