How to see what query is being passed to SQL from VB.net code

Question

I have a GetUSerID function that runs a SQL query to get the UserID.The query works and returns a userID, but I don't think it is applying the WHERE condition. IT simply returns the highest UserID in the Users table. (I tested this by adding a new user (thus the highest userID changed) then logging in again- and the displayed userId increased top the NOW highest userID. I believe it is just doing "seclect userID from users" and grapping the first result and the "WHERE username=" is not being applied. I know the process I am using for getting the username works- as when I declare that as a varibale and just display it straight away- the username is correct- but it doesn't display the useRID that goes along with that username.

i'm looking for a way to see the exact query that is being passed to SQL while i'm running the app. Is there anyway in visual studio 2015 to run a SQL trace or something while in debug mode?

The code is...

Private Function GetUserID(userName As String) As Integer
    Dim userId As Integer
    Using con As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\BandDatabase.mdf;Integrated Security=True")
        Dim comm As New SqlCommand("select UserID from Users where username = userName", con)
        comm.Parameters.AddWithValue("userName", userName)
        comm.CommandType = CommandType.Text
        con.Open()
        Try
            Dim reader As SqlDataReader = comm.ExecuteReader()
            If reader.HasRows Then
                While (reader.Read)
                    userId = reader.GetInt32(0)
                End While
                reader.Close()
                Return userId
            Else
                'no user found
                Return -1
            End If
        Catch e As Exception
            Throw New Exception("An exception has occurred: " + e.Message)
        Finally
            con.Close()
        End Try
    End Using
End Function

I know the username works, becuase I can call it this way...

userName = System.Web.HttpContext.Current.User.Identity.Name

And it picks up the right name.

However, when I call my function in page load to display....(as shown below) it is only picking up the highest userID...

Sub Page_Load(ByVal Sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    Dim userID As Integer = GetUserID(System.Web.HttpContext.Current.User.Identity.Name)

    'Displays a correct looking userID but not sure it is updating correctly 
    lblStudent.Text = userID
End Sub

Debug>Windows>Locals. But your query isn't passing a string or variable, you are comparing the column `username` to itself, which returns all the rows. — Jacob H, Dec 04 '17 at 17:51
Would calling this at pageLoad change what is being looked at in SQL at all? Dim userID As Integer = GetUserID(System.Web.HttpContext.Current.User.Identity.Name) — Robert von Mehren, Dec 04 '17 at 17:53
Focus on what I'm saying, it has nothing to do with pageload. `select UserID from Users where username = userName` these two username values are column references, your data is not being passed to this query. You are selecting a user id where the username column equals itself. Which is always. Hence why you get the latest record ID in the table. Try it in SQL and you will see. You need to use "@username" as a parameter in the vb code. — Jacob H, Dec 04 '17 at 17:55
@RobertvonMehren - listen to what Jacob H is telling you. He's correct. Your query is **INCORRECT**. — STLDev, Dec 04 '17 at 18:29

score 2 · Answer 1 · answered Dec 04 '17 at 18:33

To continue using parameters, use parameters properly. Note the added @

Dim comm As New SqlCommand("select UserID from Users where username = @userName", con)
comm.Parameters.AddWithValue("@userName", userName)

Without the @, your query is simply in the form

SELECT A.B FROM A WHERE A.C = A.C

which would just return the entire table, and in your code since you iterate the results and return the last one, it would just return UserID of the last row in the table.

How to see what query is being passed to SQL from VB.net code

1 Answers1