CODEWITHSUNDEEP
sql-servervb.netwhere-clause
chobowski
chobowski

Reputation: 109

How to put SQL WHERE clause with integer value in VB.Net?

Okay I'm sorry if this was too easy for you but I'm kinda confused and I don't know what approach I need for this. Please bear with me.

In SQL Server Management Studio I have a query that gets the active User information.

SELECT DISTINCT UserID FROM tblUserInfo WHERE IsActive = '1'

My problem is that I don't know or rather I forgot how to put where clause with integer value in VB.Net.

Here's my code

Public Shared Function FnGetUserID() As List(Of String)
        Dim lstUserID As New List(Of String)

        Dim cmd As New SqlCommand()
        Try
            cmd.Connection = DBConn.getConn()
            cmd.CommandType = CommandType.Text
            cmd.Connection.Open()

            Dim sql As String = ""
            sql += " SELECT DISTINCT UserID FROM tblUserInfo"

            cmd.CommandText = sql

            Dim sqlRdr As SqlDataReader = cmd.ExecuteReader
            While sqlRdr.Read
                lstUserID.Add(sqlRdr.Item("UserID").ToString)
            End While

            cmd.Connection.Close()
            cmd.Dispose()
        Catch ex As Exception
            cmd.Dispose()
            'lstSite = Nothing
        End Try

        Return lstUserID
    End Function

and I came up with this

Dim sql As String = ""
sql += " SELECT DISTINCT UserID FROM tblUserInfo WHERE IsActive = @IsActive"

cmd.CommandText = sql

parameter.ParameterName = "@IsActive"
parameter.Value = "1"
cmd.Parameters.Add(parameter)

I don't know if this is right or not. Please help me with this. Thanks.

Upvotes: 0

Views: 1758

Answers (2)

Jordan Robins
Jordan Robins

Reputation: 68

Your code looks correct.

If the column IsActive is an Integer, you can set the command text to be either of the following:

"SELECT DISTINCT UserID FROM tblUserInfo WHERE IsActive = 1"

"SELECT DISTINCT UserID FROM tblUserInfo WHERE IsActive = '1'"

If you want to use the parameter, you are correct with using the command text:

"SELECT DISTINCT UserID FROM tblUserInfo WHERE IsActive = @IsActive"

When using the parameter you have to make sure you add it to the sql command object as you are doing:

    Dim parameter As SqlClient.SqlParameter = New SqlClient.SqlParameter

    parameter.ParameterName = "@IsActive"
    parameter.Value = "1"
    cmd.Parameters.Add(parameter)

You must add the parameter to the command before you call ExecuteReader.

Upvotes: 1

xGeo
xGeo

Reputation: 2139

You can use Parameters.AddWithValue

Dim sql = "SELECT DISTINCT UserID FROM tblUserInfo WHERE IsActive = @IsActive"

cmd.CommandText = sql

Dim isActive = 1

cmd.Parameters.AddWithValue("@IsActive", isActive)

Upvotes: 0

Related Questions