B4J Question SQL Query - Detect if value is not in database

Discussion in 'B4J Questions' started by aaronk, Feb 13, 2015.

  1. aaronk

    aaronk Well-Known Member Licensed User

    Hi,

    I am using a SQL Query like the following:

    Code:
    Dim Value as String
    Value = LoadSQL.ExecQuerySingleResult(
    "SELECT Surname FROM users WHERE ClientName='John'")
    log(Value)
    In my database the table 'users' has the ClientName Set to 'John' and the Surname set to 'Smith'.
    In my example above the 'Value' should log 'Smith', which it does.

    However lets say the ClientName is set to 'Bill' and that doesn't exist in the database it seems to return 'null' since

    Is there a way to make 'value' log something like 'no user in database' instead ?

    The only way I see doing this is having something like:

    Code:
    If Value = "null" then Value = "No such user"
    Or, What I should be looking for is something like this SQL Query:

    SELECT Surname FROM users WHERE ClientName='john' IF clientName NOT IN users THEN Surname='Not in database'
     
    Last edited: Feb 13, 2015
  2. wonder

    wonder Expert Licensed User

    Hi!

    I'm new to SQL, but this might work:
    Code:
    UserCheck = LoadSQL.ExecQuerySingleResult("SELECT COUNT(*) AS User_Exists FROM users WHERE ClientName='John'")
    If 'John' doesn't exist on the given table, the count will be zero.
    Code:
    If UserCheck = 0 Then Value = "No such user"
     
  3. eps

    eps Well-Known Member Licensed User

    almost right it's something like... no need for the AS part and you can insert a Value to search for as opposed to hardcoding, but I'm sure you've worked that out already :)

    Code:
    UserCheck = LoadSQL.ExecQuerySingleResult("SELECT count(*) FROM users where series ClientName = '" & Value & "'")
    [\code]

    but you may well have 
    to tweak to suit your environment / App
     
    wonder likes this.
  4. rwblinn

    rwblinn Well-Known Member Licensed User

    Hi,

    alternative is to use a sub like:
    Code:
    Sub getClientName(name As StringAs String
        
    Dim result As String = "Not in Database"
        
    Dim cursor As ResultSet
        
    Try
            
    cursor = gSQL.ExecQuery2("SELECT ClientName FROM users WHERE ClientName=?",Array As String(name))
            
    Do While cursor.NextRow
                result = 
    cursor.GetString2(0)       
            
    Loop
        
    Catch
            
    Log(LastException.Message)
        
    End Try
        
    Return result
    End Sub
     
  5. aaronk

    aaronk Well-Known Member Licensed User

    Thanks for your help guys..
    I ended up using something like this:
    Code:
    Dim Value as String = "John"
    Dim UserLastName as String
    UserCheck = LoadSQL.ExecQuerySingleResult(
    "SELECT count(*) FROM users WHERE ClientName = '" & Value & "'")
    If UserCheck = 0 Then
          
    Log("User is Not in database")
       
    Else
          
    Log("User is in database")
          
    Dim Value asString
          UserLastName = LoadSQL.ExecQuerySingleResult(
    "SELECT Surname FROM users WHERE ClientName = '" & Value & "'")
          
    Log(UserLastName)
    End If
     
    eps likes this.
  6. hbruno

    hbruno Member Licensed User

    Test this :
    Code:
    Dim Value as String
    Dim UserLastName as String = LoadSQL.ExecQuerySingleResult("SELECT Surname FROM users WHERE ClientName = '" & Value & "'")
    if UserLastName != Null Then
        
    Log(UserLastName)
    else
        
    Log(Value & " is Not in database")
    end if
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice