Results 1 to 9 of 9

Thread: Check to see if username is in the results of a sql query

  1. #1

    Thread Starter
    Member
    Join Date
    Dec 2011
    Posts
    32

    Check to see if username is in the results of a sql query

    I have a windows forms application that need to check to see if a user has access to a specific form before it loads.

    I have the sql query working in management studios, now I need to get the results into VB in a list or array or something else that will work.

    I know how to get the windows username, that isn't a problem.

    This Query works like I need it to in Management Studio:

    Code:
    SELECT  Attendance.UserName
    FROM Attendance INNER JOIN
               Reviews ON Attendance.ReviewID = Reviews.ReviewID
    WHERE reviewdate = Convert(date, getdate())
    The results from this query will generally be less than 10 names.

    I am going through a brain block, I can't seem to work out the best way to get the sql results into a simple format in vb to check against.

    Thank you,

    Steve Hathaway

  2. #2

    Thread Starter
    Member
    Join Date
    Dec 2011
    Posts
    32

    Re: Check to see if username is in the results of a sql query

    I just thought of something and want to know what you think about this idea.

    What if I use the below sql query passing in the 'username' as I generate the sql statement in VB,
    Then I can check to see if the row count is > 0 for a match.


    Code:
    SELECT        Attendance.UserName
    FROM            Attendance INNER JOIN
                             Reviews ON Attendance.ReviewID = Reviews.ReviewID
    WHERE        (reviewdate = Convert(date, getdate())) and attendance.username = 'username'
    Last edited by shathaway; Sep 28th, 2017 at 11:05 AM.

  3. #3
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: Check to see if username is in the results of a sql query

    Hi,
    One option is create a List(Of String) that contains the forms the user can open and fill it at startup.
    Then you just have to check if the form name is that list.

    Dim ListOfFormNames as ew List(Of String)
    If ListOfFormNames.contains(FormName) Then

    code to open your form

    End if

  4. #4

    Thread Starter
    Member
    Join Date
    Dec 2011
    Posts
    32

    Re: Check to see if username is in the results of a sql query

    This will work, how do I get the results from the sql query into the list?

    Thank you,

    Steve

  5. #5
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: Check to see if username is in the results of a sql query

    Untested code, you need to perfect it:


    Code:
    Dim LSt As New List(Of String)
            Dim CNN_STR As String = "Your connection string"
            Dim SQLCMDTXT As String = "You Select command"
            'Creates a SQL connection and disposes of it in the end of code execution
            Using CNN = New SqlConnection(CNN_STR)
                'Creates a SQL command  and disposes of it in the end of code execution
                Using cmd = New SqlCommand(SQLCMDTXT, CNN)
                    CNN.Open()
    
                    Try
                        'Executes the SQL command
                        Dim dr = cmd.ExecuteReader()
                        While dr.Read()
                            'Adds entries to the list
                            LSt.Add(dr("Column name here(that contains the forms name)").ToString())
                        End While
                    Catch ex As SqlException
                        ' Do some logging or something. 
                        MessageBox.Show(ex.Message)
                    End Try
                End Using
            End Using
    Last edited by Mike Storm; Sep 28th, 2017 at 11:55 AM.

  6. #6
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Check to see if username is in the results of a sql query

    Quote Originally Posted by shathaway View Post
    I just thought of something and want to know what you think about this idea.

    What if I use the below sql query passing in the 'username' as I generate the sql statement in VB,
    Then I can check to see if the row count is > 0 for a match.


    Code:
    SELECT        Attendance.UserName
    FROM            Attendance INNER JOIN
                             Reviews ON Attendance.ReviewID = Reviews.ReviewID
    WHERE        (reviewdate = Convert(date, getdate())) and attendance.username = 'username'
    I'd tend to do it that way, but I'd use COUNT(Attendance.UserName). Then use ExecuteScalar to get a single value back, which will be either a 0 (no count), or 1 (there was a match).
    My usual boring signature: Nothing

  7. #7
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Check to see if username is in the results of a sql query

    There is some value to getting the set of forms the user can access, as that is just one round trip to the DB per user. That may or may not cause issues, depending on whether the user could change in mid stream. However, a single scalar query is so fast and light as to barely matter, so it largely comes down to what is easier.

    A third option is to give every user a roll. They could get their roll upon startup. Every form would then be able to decide whether they can be shown to a user in that roll. That would mean that the DB would hold perhaps just an integer per user, and the forms wouldn't have to be stored anywhere, as each form could know that if the user in rull R, they are okay, otherwise they are not.
    My usual boring signature: Nothing

  8. #8
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Check to see if username is in the results of a sql query

    Quote Originally Posted by Shaggy Hiker View Post
    There is some value to getting the set of forms the user can access, as that is just one round trip to the DB per user. That may or may not cause issues, depending on whether the user could change in mid stream. However, a single scalar query is so fast and light as to barely matter, so it largely comes down to what is easier.

    A third option is to give every user a roll. They could get their roll upon startup. Every form would then be able to decide whether they can be shown to a user in that roll. That would mean that the DB would hold perhaps just an integer per user, and the forms wouldn't have to be stored anywhere, as each form could know that if the user in rull R, they are okay, otherwise they are not.
    Roll? Rull? Someone replace your e key with a spare l?

    Unless you plan to break break... in which case sourdough rulls!

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Check to see if username is in the results of a sql query

    That's how I roll. You just know that when the roll is called up yonder, I'll b thr.
    My usual boring signature: Nothing

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width