[RESOLVED] ExecuteScalar tricking me.

[maps]

I want to loop through my table and extract a UserName that corresponds with the UserName typed in a text box using the execute scarlar method.

If it's there then i call a message and exit the sub, if its not there then i add the User.

Public Shared Function LookUp( ByVal strSQL As String) As String
        Dim strValue As String = ""
        Dim myConn As OleDbConnection = New OleDbConnection(strAccessConn)
        Dim myCommand As New OleDbCommand(strSQL, myConn)
        Try
            myConn.Open()
            strValue = CStr(myCommand.ExecuteScalar()) 'Option strict is on
            myConn.Close()
        Catch ex As Exception
            MsgBox("Error:" & ex.Message)
        End Try
        Return strValue
    End Function

Here is my SQL

Dim  strUserName as string="SELECT UserName FROM Users WHERE(UserName='" & Me.tbUserName.text.Trim() & "')"

My Execute scalar returns a string but i cant get the string when i call it like this. It Returns an empty messagebox

MessageBox.Show(LookUp(strUserName).toUpper)

[dRAMmer]

I think you should remove the first call to ExecuteScalar method, anyway did you try the query if it really returns a row?

[maps]

Yap. it returns a row. I tried it in the access db it self.

[maps]

Errr.... sorry. the First executeScalar shouldnt be there. It was a typo.
But the problem still stands

[maps]

The Error is Object Reference not set to an Instance Of an Object...!!!

[dRAMmer]

Can you paste the code that you really have.

[maps]

I Edited the one above and thats the one i have.

[jmcilhinney]

How about using this:

Dim myCommand As New OleDbCommand("SELECT COUNT(*) FROM Users WHERE UserName = @UserName", myConnection)
 
myCommand.Parameters.AddWithValue("@UserName", Me.tbUserName.Text.Trim())
 
Try
    myConnection.Open()
 
    If CInt(myCommand.ExecuteScalar()) = 0 Then
        'No user with that username.
    End If
Finally
    'You MUST close the connection in a Finally block or it will not be closed if an exception is thrown.
    myConnection.Close()
End Try

[dRAMmer]

Well there goes a good suggestion and I couldn't agree more

[maps]

Works ok. But how can i handle the validation.... ToUpper part of it.
In case the user name in the table is lowercase and the person enters the same username but in Uppercase. it will show that it isnt there!!

With my LookUp function i would do it like this.

If Not connMger.LookUp(strDupUserName).ToUpper = Trim(Me.tbUserName.Text).ToUpper Then
MessageBox.Show("UserName Exists")
Exit Sub
Else
AddUser()
End If

[jmcilhinney]

If queries are case sensitive then you can do this:

Dim myCommand As New OleDbCommand("SELECT COUNT(*) FROM Users WHERE UPPER(UserName) = @UserName", myConnection)
 
myCommand.Parameters.AddWithValue("@UserName", Me.tbUserName.Text.Trim().ToUpper())
 
Try
    myConnection.Open()
 
    If CInt(myCommand.ExecuteScalar()) = 0 Then
        'No user with that username.
    End If
Finally
    'You MUST close the connection in a Finally block or it will not be closed if an exception is thrown.
    myConnection.Close()
End Try

[maps]

Thanks but it doesnt work out.
The Error Messager is, undefined function UPPER in Expression.

[jmcilhinney]

Hmmm... That was T-SQL (SQL Server) and unfortunately Jet SQL, which you will be using if your database is Access, does not support UPPER. Interestingly it does have a LOWER reserved word, but I'm not sure if it will convert a string to lower case. You could try changing UPPER to LOWER and ToUpper to ToLower and see if it works. if not then it might be worth a look through the Jet SQL reference to see if there is something available that will do the job.