How to put SQL query WHERE clause with integer value in VB.NET? Debugging window service?

Question

0.00/5 (No votes)

See more:

Okay I'm sorry if this was too easy for you but I'm kinda new to programming so I don't know what approach I need for this.

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

SQL

SELECT DISTINCT UserID FROM tblUserInfo WHERE IsActive = '1'

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

VB

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

Hope you can help me with this.

And also I'm doing a Project Window Service and I'm also new with this so I figure when I want to test my project I also just click the start debugging button in VB studio and the Window Service will run and debug but when I click it there's a window pop-up saying "Windows Service Start Failure" and "Cannot start service from the command line or a debugger. A Windows Service must first be installed (using installutil.exe) and then started with the ServerExplorer, Windows Services Administrative tool or the NET START command."

What does that mean? And am I doing the right way of debugging my Window Service?

What I have tried:

Ah. I tried this code

VB

sql += " SELECT DISTINCT UserID FROM tblUserInfo WHERE IsActive = '" & 1 & "' "

Well I'm really clueless on how I could do this.

Posted 28-Feb-17 14:18pm

RKeyy Sii

Updated 1-Mar-17 22:23pm

Add a Solution

Comments

[no name] 28-Feb-17 20:27pm

You use a parameterized query.

RKeyy Sii 28-Feb-17 21:22pm

like 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)

[no name] 28-Feb-17 20:33pm

Oh and "What does that mean?" it means exactly what it says. Service are under the control of the Service Control Manager and can't be started from the command line like a normal exe.
"And am I doing the right way of debugging my Window Service", obviously not. You can attach to your service process or what I do is write code that starts the service in "debug" mode that doesn't rely on the SCM so that it can be debugged normally.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gregorio89 · Accepted Answer · 2017-03-01T22:23:00

you can write your code in this way

dim sql as string= " SELECT DISTINCT UserID FROM tblUserInfo WHERE IsActive = 1" 

this beacause your value is an integer and sql doesn't want ' for integer.

for debug your windows service you can use this tutorial, although you should use a windows application for testing your code (you can change a little of your code in debug mode).

<a href="https://www.youtube.com/watch?v=pfF-GNK5ygE">How to Debug Windows Service Source Code in Microsoft Visual Studio - YouTube</a>[<a href="https://www.youtube.com/watch?v=pfF-GNK5ygE" target="_blank" title="New Window">^</a>]


I hope it was useful