Oracle Data access question

[DougGerlach]

i have the following code

Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports System.Data
Imports System.Data.Common

Public Class DataClass

    Private _tadb As Microsoft.Practices.EnterpriseLibrary.Data.Database = _
        Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase("TADBConnectionString")

    Private Shared ReadOnly _instance As DataClass = New DataClass

    ' Entry point into this singleton
    Public Shared ReadOnly Property Instance() As DataClass
        Get
            Return _instance
        End Get
    End Property

    Public Shared Function ValidLogin(ByVal id As String, ByVal pass As String) As String
        'Dim retval As Boolean = False
        Dim sqlQuery As String = "select groupid from security where login_id = '" & id & "' and [password] = '" & pass & "'"
        Dim command As DbCommand = _instance._tadb.GetSqlStringCommand(sqlQuery)
        Dim retVal As String = _instance._tadb.ExecuteScalar(command)
        command.Connection.Close()
        command.Dispose()
        Return retVal


        Return retval
    End Function

End Class

when i run the code i expect to get back a value for the groupid that i query, but i get nothing. when i go and remove the where clause of the query it returns a record. i know my where clause is correct with the proper field names and values. has anyone ever seen this before?
thanks
doug

 

[cjard]

If this:

SELECT MAX(groupid) FROM security

returns a result but this:

SELECT groupid FROM security WHERE id = 'hello' and [password] = 'world'

returns nothing (an empty string? null? what?) then i'm afraid that your where clause is wrong. Oracle doesnt lie, you have, without a doubt and 100% guaranteed gotten the where clause wrong. Perhaps one of the query columns is a CHAR type and has trailing spaces (WHERE id = 'hello '), or youre failing to note the case sensitivity of oracle, or the groupid for that record is null.. THere may be other reasons, but you'll only find it if you dig around with an sql editor

Please take a read of the DW2 link in my signature, section on Creating a Simple Data App (youll need to adapt some advice for oracle) for a guide on how to do oracle data access properly.. this is very old db code you wrote here