[RESOLVED] ExecuteScalar tricking me.
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.
VB Code:
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
VB Code:
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
VB Code:
MessageBox.Show(LookUp(strUserName).toUpper)
Re: ExecuteScalar tricking me.
I think you should remove the first call to ExecuteScalar method, anyway did you try the query if it really returns a row?
Re: ExecuteScalar tricking me.
Yap. it returns a row. I tried it in the access db it self.
Re: ExecuteScalar tricking me.
Errr.... sorry. the First executeScalar shouldnt be there. It was a typo.
But the problem still stands
Re: ExecuteScalar tricking me.
The Error is Object Reference not set to an Instance Of an Object...!!!
Re: ExecuteScalar tricking me.
Can you paste the code that you really have.
Re: ExecuteScalar tricking me.
I Edited the one above and thats the one i have.
Re: ExecuteScalar tricking me.
How about using this:
VB Code:
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
Re: ExecuteScalar tricking me.
Well there goes a good suggestion and I couldn't agree more :)
Re: ExecuteScalar tricking me.
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.
VB Code:
If Not connMger.LookUp(strDupUserName).ToUpper = Trim(Me.tbUserName.Text).ToUpper Then
MessageBox.Show("UserName Exists")
Exit Sub
Else
AddUser()
End If
Re: ExecuteScalar tricking me.
If queries are case sensitive then you can do this:
VB Code:
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
Re: ExecuteScalar tricking me.
Thanks but it doesnt work out.
The Error Messager is, undefined function UPPER in Expression.
Re: ExecuteScalar tricking me.
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.