I cannot figure out why this will not execute. For somereason it does not like my inner join I believe but it works perfectly in management studio. Any ideas?

Dim column_1 As String
        Dim column_2 As String
        Dim column_3 As String
        Dim TotalCol As String
        Dim D As String
        Dim M As String
        Dim Y As String

        D = DateTime.Now.Day
        M = Format(Month(Now), "00")
        Y = DateTime.Now.Year
        'creat a text file first
        Dim oWriter As System.IO.StreamWriter
        oWriter = System.IO.File.CreateText("c:\sample1_" & Y & M & D & ".txt")
        'connection string and mysqlconnect
        Dim connstring As String = "Server=11.14.2.31\TESTSQLSRVR;Database=TEST;User ID=sa;Password=TestPass;Trusted_Connection=False;"
        Dim conn As New SqlConnection(connstring)
        'create selection query
        Dim selectSQL As String = "select Servicenumber,pilotarea,propertydatamap" & _
                                    "from latipping a inner join lalocation b on a.locationid = b.locationid " & _
                                    "left join laitem c on a.itemid = c.itemid" & _
                                    "where(tipdatetime > getdate() - 1)" & _
                                   "and Observation1 <> '' " & _
                                   "order by streetname,cast(streetnumber as int)"

        'create selection command
        Dim cmd As New SqlCommand(selectSQL, conn)
        'set cmd property
        cmd.CommandType = CommandType.Text
        'open database
        conn.open()
        'create a new reader and execute the reader
        Dim myreader As SqlDataReader = cmd.ExecuteReader
        'read information from the database and give the values to the arguments(column_1, and column_2)
        While myreader.read
            column_1 = myreader.getstring(0)
            column_2 = myreader.GetString(1)
            column_3 = myreader.GetString(2)
            TotalCol = column_1 + "|" + column_2 + "|" + column_3
            oWriter.WriteLine(TotalCol)
        End While
        'close file
        oWriter.Close()
    End Sub

It will be much easier for you to debug things like this by adding

Debug.Print selectSQL

after you've set selectSQL or what you call your var.
While executing you'll get the actual query sent to SQL in the immediate pane.
Copy/Pasting this to the db will always show if your query is working or if you've missed a couple spaces like in this case.

This article has been dead for over six months. Start a new discussion instead.