Cannot Connect to SQL Database

[mpond]

Hi,

I'm rather new to VB but am having an issue connection to SQL. My code is below and, while I think it's fine, the error I receieve is"[Microsoft][ODBC driver for Oracle][Oracle]ORA-00936: missing expression." Can someone please tell me what might be the issue? Thanks in advance.

Option Explicit On
Imports System.Math
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop.Excel
Imports ADODB
Module Module1


    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    'Dim misValue As Object = System.Reflection.Missing.Value

    Dim rsGetData As New ADODB.Recordset
    Dim sSql As String
    Dim rows As Integer
    Dim file1s As String
    Public cnnOracle As New ADODB.Connection

    Sub Main()




        xlApp = New Excel.Application
        'xlWorkBook = xlApp.Workbooks.Add(misValue)

        xlApp.Visible = True
        xlApp.DisplayAlerts = False
        xlApp.ScreenUpdating = True

        xlWorkBook = xlApp.Workbooks.Add
        xlWorkBook.Sheets("sheet1").Name = "Exceptions"
        xlWorkBook.Sheets("Exceptions").Range("A4").Value = "Trade Date"
        xlWorkBook.Sheets("Exceptions").Range("B4").Value = "Price"
        xlWorkBook.Sheets("Exceptions").Range("C4").Value = "Quantity"
        xlWorkBook.Sheets("Exceptions").Range("D4").Value = "Base Currency"

        xlWorkBook.Sheets("Exceptions").Range("A4:D4").Interior.ColorIndex = 11
        xlWorkBook.Sheets("Exceptions").Range("A4:D4").Font.ColorIndex = 44
        xlWorkBook.Sheets("Exceptions").Range("A4:D4").Font.Bold = True

        sSql = "select select zen.tradedate, zen.price, zen.quantity, zen.base_currency"
        sSql = sSql & " from tca_zeno_bestex zen"
        sSql = sSql & " where zen.fund_name = 'OCM'"
        sSql = sSql & "and zen.source is null"

        Try

            'Open Oracle connection
            With cnnOracle
                .ConnectionString = "DRIVER={Microsoft ODBC for Oracle};SERVER=BT_PRDREP.world;UID=etf_user;PWD=etf_user;"
                .CursorLocation = CursorLocationEnum.adUseClient
                .Open()
            End With

            rsGetData = New ADODB.Recordset
            'Open recordset and print data records to Excel
            rsGetData.Open(sSql, cnnOracle, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly)

            If Not rsGetData Is Nothing Then

                xlWorkSheet = xlWorkBook.Sheets("Exceptions")
                xlWorkSheet.Activate()
                rows = 0

                While Not rsGetData.EOF
                    xlWorkSheet.Range("A5").Offset(rows, 0).Value = rsGetData("tradedate").Value
                    xlWorkSheet.Range("A5").Offset(rows, 1).Value = Format(rsGetData("price").Value, "#,#00")
                    xlWorkSheet.Range("A5").Offset(rows, 2).Value = Format(rsGetData("quantity").Value, "#,#00")
                    xlWorkSheet.Range("A5").Offset(rows, 3).Value = Format(rsGetData("base_currency").Value, "#,#00")

                    rsGetData.MoveNext()
                    rows = rows + 1

                End While

                rsGetData.Close()

            End If

            xlWorkSheet.Columns.AutoFit()

        Catch ex As Exception

            MsgBox("Description: " & Err.Description & ", " & "Number: " & Err.Number, vbOKOnly, "Error")

        Finally

            cnnOracle.Close()

        End Try

    End Sub

End Module

[dunfiddlin]

Do you not need to specify the database you want to use on the server?

[MattP]

sSql starts with select select.

[mpond]

Thanks Matt, that's all it was. Silly mistake.

[MattP]

No problem, we've all stared at a monitor for way too long with something silly wrong and not been able to see it. Sometimes it just takes an outside eye.

Personally I like to have the keywords in all caps so I can pick them out easier at a glance. It helps me avoid issues like this.