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

thenthorn1010Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
       mDetailOutput.Append("Select '12' As OrderQuantity, ")


You are continuing the detail select from the line above . . . should only be one select in the detail until the UNION ALL . . . then a new select.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thenthorn1010Author Commented:
Thanks for the help...this was something I missed when constructing the SQL statements
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.