-
Sep 28th, 2017, 10:20 AM
#1
Thread Starter
Member
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
-
Sep 28th, 2017, 10:45 AM
#2
Thread Starter
Member
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.
-
Sep 28th, 2017, 10:54 AM
#3
Hyperactive Member
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
-
Sep 28th, 2017, 11:01 AM
#4
Thread Starter
Member
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
-
Sep 28th, 2017, 11:42 AM
#5
Hyperactive Member
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.
-
Sep 28th, 2017, 12:06 PM
#6
Re: Check to see if username is in the results of a sql query
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).
My usual boring signature: Nothing
-
Sep 28th, 2017, 02:10 PM
#7
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
-
Sep 28th, 2017, 03:15 PM
#8
Re: Check to see if username is in the results of a sql query
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
-
Sep 28th, 2017, 04:26 PM
#9
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|