(RESOLVED) ADO - Whats wrong? pls have a look

[Bongo]

Greetings,

What is wrong with the following code?

Every time run it I get in the line

an error message (see below)

Option Explicit

' The database connection.
Private m_Conn As ADODB.Connection

Private Const CONNECT_STRING  As String = "Provider=OraOLEDB.Oracle.1;Password=XXXX;Persist Security Info=True;User ID=XXXX;Data Source=XXXX"

Public Function GetStatusSTR(ByVal vName As String, ByVal vPW As String) As Integer
Dim rs As ADODB.Recordset
Dim strSQL As String
    strSQL = "SELECT S.USER_Status FROM ST_USER_STATUS S, ST_USER_ACCESS A WHERE "
    strSQL = strSQL & "A.STATUS = S.USER_STATUS_ID AND A.USERNAME = '" & vName
    strSQL = strSQL & "' AND A.PASSWORD = '" & vPW & "';"
    Set rs = m_Conn.Execute(strSQL)
    If rs.RecordCount > 0 Then
        GetStatusSTR = rs(0)
    Else
        GetStatusSTR = 0
    End If
    rs.Close
    Set rs = Nothing
    Exit Function
End Function

Private Sub Class_Initialize()
    ' Open the database connection.
    Set m_Conn = New ADODB.Connection
    m_Conn.ConnectionString = CONNECT_STRING
    m_Conn.Open
End Sub

Private Sub Class_Terminate()
    m_Conn.Close
    Set m_Conn = Nothing
End Sub

The error message is
RunTime Error "-2147217900 (80040e14)
ORA-00911:

strSQL containes the following value when Set rs = m_Conn.Execute(strSQL) is executing
"SELECT S.USER_Status FROM ST_USER_STATUS S, ST_USER_ACCESS A WHERE A.STATUS = S.USER_STATUS_ID AND A.USERNAME = 'zzz13204' AND A.PASSWORD = 'admin';"

Many thanks in advance

[ae_jester]

try including the fields that you use in your WHERE clauses in the SELECT fieldname1, fieldname2 etc

[Bongo]

ae_jester, nop did not work same error no

[Bongo]

It was the semi colon on the end of the strSQL. After removing it worked

[ae_jester]

yeah that will do it :-)

[Wokawidget]

That wouldn't affect it at all...what line of code is it crashing on?
Is it this one:

Set rs = m_Conn.Execute(strSQL)

You could always try this code:

strSQL = "SELECT ST_User_Status.User_Status "
    strSQL = strSQL & "FROM ST_User_Access "
    strSQL = strSQL & "INNER JOIN ST_User_Status ON ST_User_Status.User_Status_ID = ST_User_Access.Status "
    strSQL = strSQL & "WHERE ST_User_Access.Username = '" & vName & "' "
    strSQL = strSQL & "AND ST_User_Access.Password = '" & vPW & "' "
    Set rs = New Recordset
    With rs
        .CursorLocation = adUseClient
        .Open strSQL, m_Conn, adOpenForwardOnly, adLockReadOnly
        Set .ActiveConnection = Nothing
        If .EOF Then
            GetStatusSTR = 0
        Else
            GetStatusSTR = .Fields("User_Status").Value
        End If
    End With
    Set rs = Nothing

Hope this helps...

Woka

[Wokawidget]

Sorry, took ages to post that, I was refering to placing the search fields in the select statement
Although the code above is alot clearer to understand

Adios,

Woka