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

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'

---

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

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

Thank you,

Steve

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

---

Quote:

---

Originally Posted by shathaway

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

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.

Quote:

---

Originally Posted by Shaggy Hiker

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

That's how I roll. You just know that when the roll is called up yonder, I'll b thr.