Error filling dataset with OracleDataAdapter in VB.NET

[Tom Wells]

I have what was supposed to be a simple program to pull data from one Oracle
server and stuff it into a table on another Oracle server. Filling the
DataSet works on the first server but on the second it fails. I'm using a
seperate class for each database. I am totally stumped! The error Oracle
returns is: ORA-00936: "missing expression". The da.Fill(ds) line is
identical in both classes so I don't get why one works and the other gives
this rather meaningless error message. If you can help I will be most
grateful.
Here is my code:

Form1.VB

Public Class Form1
Inherits System.Windows.Forms.Form
Dim dr1 As DataRow
Dim windDataDB As OSI_DataDB
Dim dr2 As DataRow
Dim hourlyDataDB As Set_WTD_Hourly_DataDB

#Region " Windows Form Designer generated code "
'I didn't touch the area!
#End Region

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
hourlyDataDB = New Set_WTD_Hourly_DataDB
windDataDB = New OSI_DataDB
Dim ds1 As DataSet = windDataDB.GetOSIData
Dim ds2 As DataSet = hourlyDataDB.GetWindData
For index As Integer = 0 To ds1.Tables(0).Rows.Count
dr1 = ds1.Tables(0).Rows(index)
dr2("interval_data" & Trim(CStr(index))) = Trim(dr1("value") & " ")
dr2("time_label" & Trim(CStr(index))) = Trim(dr1("time") & " ")
Next
dr2("LATESTUPDATE") = Format(Now(), "MM/dd/yyyy hh:mm tt")
hourlyDataDB.UpDateWindData(ds2)
End Sub
End Class

OracleDataAccess.VB

Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Public Class OSI_DataDB
Implements IDisposable
Public IsDisposed As Boolean = False
Dim cn As OracleConnection
Sub New()
cn = New OracleConnection("Data Source=oServ1; User Id=user1;
Password=password1;")
End Sub
Public Function GetOSIData() As DataSet
Dim dRightNow As DateTime = Now()
Dim dStartTime As DateTime = DateAdd(DateInterval.Hour, -24, dRightNow)
Dim sStartTime As String
Dim sTableName As String
Dim sTableName2 As String
Dim iNowYear As Integer
Dim iNowMonth As Integer
Dim iStartYear As Integer
Dim iStartMonth As Integer
Dim iStartDay As Integer
Dim iStartHour As Integer
Dim sNowYear As String
Dim sNowMonth As String
Dim sStartYear As String
Dim sStartMonth As String
Dim sStartDay As String
Dim sStartHour As String
Dim SQL As String

iNowYear = Year(dRightNow)
iNowMonth = Month(dRightNow)
iStartYear = Year(dStartTime)
iStartMonth = Month(dStartTime)
iStartDay = Microsoft.VisualBasic.DateAndTime.Day(dStartTime)
iStartHour = Hour(dStartTime)
sNowYear = CStr(iNowYear)
sNowMonth = CStr(iNowMonth)
sStartYear = CStr(iStartYear)
sStartMonth = CStr(iStartMonth)
sStartDay = CStr(iStartDay)
sStartHour = CStr(iStartHour)
sStartTime = sStartYear & "-" & sStartMonth & "-" & sStartDay & "-" &
sStartHour
sTableName = "OSI.data_values_" & sStartMonth & "_" & sStartYear
sTableName2 = "OSI.data_values_" & sNowMonth & "_" & sNowYear
If iStartYear <> iNowYear Or iStartMonth <> iNowMonth Then
SQL = "select to_char(time, 'YYYY-MM-DD-HH24-MI-SS'), value From (select
* from " & sTableName & "Where osi_key = '05169901' Union select * from " &
sTableName2 & "Where osi_key = '05169901') Where time > to_date('" &
sStartTime & "', 'YYYY-MM-DD-HH24') order by time;"
Else
SQL = "Select to_char(time, 'YYYY-MM-DD-HH24-MI-SS'), value from " &
sTableName & " Where osi_key = '05169901' AND time > to_date('" & sStartTime
& "', 'YYYY-MM-DD-HH24') order by time"
End If
Try
Dim da As New OracleDataAdapter(SQL, cn)
Dim ds As New DataSet
da.Fill(ds) '!!! This line works great!!!!!!!!
Return ds
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Function
Public Sub Dispose() Implements System.IDisposable.Dispose
If IsDisposed = False Then
cn.Close()
GC.SuppressFinalize(Me)
IsDisposed = True
End If
End Sub
Protected Overrides Sub Finalize()
MyBase.Finalize()
Dispose()
End Sub
End Class

Public Class Set_WTD_Hourly_DataDB
Implements IDisposable
Public IsDisposed As Boolean = False
Dim cn As OracleConnection
Dim da As OracleDataAdapter
Sub New()
cn = New OracleConnection("Data Source=oServ2; User Id=user2;
Password=password2;")
End Sub
Public Function GetWindData() As DataSet
Try
Dim da As New OracleDataAdapter("Select * from wtd.hourly_generation;",
cn)
Dim ds As New DataSet
da.Fill(ds) '!!! This line FAILS!!! WAH! BOO HOO!!!!! Other sounds of
despair!!!
Return ds
Catch ex As Exception
MsgBox(ex.Message) ' The message box says- ORA-00936: "missing
expression"
End Try
End Function
Public Function UpDateWindData(ByVal ds As DataSet)
da.Update(ds)
End Function
Public Sub Dispose() Implements System.IDisposable.Dispose
If IsDisposed = False Then
cn.Close()
GC.SuppressFinalize(Me)
IsDisposed = True
End If
End Sub
Protected Overrides Sub Finalize()
MyBase.Finalize()
Dispose()
End Sub
End Class

 

[Tom Wells]

I found the problem - I actually wrote proper SQL! .NET can't handle the ;
that every other environment in the world requires at the end of an SQL
statement. When I removed the ; everything worked fine. Sometimes
Microsoft can be real pin heads.