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