I have a datagrid with rows that pertain to multiple users. I want to
write a query statement so that whomever the CURRENT logged-in
user is, he can go to the datagrid page and it will show ONLY rows
that pertain to him (programmatic security). To accomplish this,
I have a column in the datatable that references each user's
username. So I need a query statement that will compare the table's
'username' column to the current logged-in user. Something like...
SELECT... FROM [VendorEquipment]
WHERE [UserName] = Context.Current.User.Name
...or something like this. Question is, can someone help on how to
syntax this. Also, do I need anything special in the login form or in
web.config to 'pave the way' for this query to work.
Thx much.
Reid C.after a successfull login,you can store the username in sessions...|||Thanks for the reply...but I am a novice and will probably need more to clear up
the picture...can you suggest how I can syntax my SELECT command to accomplish
the above-stated objective and is there something I need to do via sessions to
enable this--and where would I do that?
Thx much.
Reid C.|||i believe you are checking for user authorization when the user logs in right ?
just put the username into session after the validation is successfull
session("username")=username
and frm any page
you can get it back as
dim uname as string
uname=session("username")
so when you query the db for the records you can do something like
strsql=select * From table where username='" & uname & "'"
and bind the datagrid.
HTH|||Thanks again...this sounds like what I'm looking for...just two
more questions due to my relative inexperience:
1) the statement: session("username")=username
you suggested...does that need to be in the login form
like in the "sub loginBtn_click" subroutine? and if so where in
the subroutine does it need to go...is the placement of the
statement critical?
2) is "username" interchangeable in this operation with "userid" ?
because I have written my registration form and login with a
"userid" instead of a "username"...but I will change this if it is
important to have "username" for forms authentication.
Thx.
Reid C.|||yep you can have any variable in session you want..username,userid...doesnt matter.
right after you check the userid with the password and if the query returns as a valid user, store the userid in sessions...and you should be able to access it frm any page...
HTH|||Thank you ...do I place the session stmt in global.asax?
like this, for instance:
Sub Session_OnStart()
Session("userId")=userId
End Sub
?
Thx.
Reid C.|||no...you put it in ur login.aspx where ever you validate the authenticity of the user...|||o.k., thanks again, ...but I am a complete novice and have been building pages
using WebMatrix, so sorry to be dense...but do I place the "session..." statement
in the "Function GetUser..." or in my "Sub LoginBtn_click..." subroutine, or after
that and right before the </script> tag ? I know placement of this statement is
important, I just don't know where?
Thx again.
Reid C.|||if you post your code for the login page..i can tell you where to place it...|||Thanks again for all your help...here is the code for the login (minus the html) and
I've got the statement placed right before the end </script> tag...tell me if you see
a glaring case of needing to move it to a more appropriate space.
<script runat="server">
Function GetUser(ByVal userId As String, ByVal userPassword As String) As System.Data.DataSet
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='VSdatastore'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "SELECT [Users].* FROM [Users] WHERE (([Users].[UserId] = @.UserId) AND ([Users].[U"& _
"serPassword] = @.UserPassword))"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_userId As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userId.ParameterName = "@.UserId"
dbParam_userId.Value = userId
dbParam_userId.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_userId)
Dim dbParam_userPassword As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userPassword.ParameterName = "@.UserPassword"
dbParam_userPassword.Value = userPassword
dbParam_userPassword.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_userPassword)
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)
Return dataSet
End Function
Sub LoginBtn_Click(Sender As Object, E As EventArgs)
If Page.IsValid Then
Dim userDS As New System.Data.DataSet
userDS = GetUser(UserId.Text, UserPassword.Text)
If userDS.Tables(0).Rows.Count = 1 Then
FormsAuthentication.RedirectFromLoginPage(UserId.Text, false)
Else
Msg.Text = "Invalid UserId or Password: Please try again"
End If
End If
End Sub
session("UserId")=UserId
</script
Thx again.
Reid C.|||i think this should work...
If Page.IsValid Then Dim userDS As New System.Data.DataSet
userDS = GetUser(UserId.Text, UserPassword.Text)
If userDS.Tables(0).Rows.Count = 1 Then
'valid user...
session("UserId")=UserId
FormsAuthentication.RedirectFromLoginPage(UserId.Text, false)
Else
Msg.Text = "Invalid UserId or Password: Please try again"
End If
End If
End Sub
session("UserId")=UserId
|||In your revision you have the "session..." statement right after
the "If userDS.Tables(0).Rows.Count = 1 Then" statement which
made sense as soon as I saw it. But you also have the same
"session..." statement again after the "End Sub" statement. Is
this just a typo on your part from copying and pasting what I
sent you, or does it need to be there?
I am assuming it is a copy&paste oversight...unless you answer
back that it needs to be repeated...
Thx much again for your help.
Reid C.|||See if you can review the code for my datagrid page and see where I'm going wrong.
One thing, the "UserId" I need to compare is in the datatable as "PropUserId" because
I also have a column in the table "VendorUserId" and need to reference both. So I
need to compare the session "UserId" with the table's "PropUserId" column and I've
added a statement in the "Function GetVendorEquipment..." that says the "PropUserId"
column is equivalent to "UserId"...but I am getting a blank table on the page when I
run it.
<script runat="server"
Function GetVendorEquipment() As System.Data.DataSet
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='VSdatastore'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
dim UserId as String
dim PropUserId as String
PropUserId=session("UserId")
Dim queryString As String = "SELECT [VendorEquipment].* FROM [VendorEquipment] WHERE [PropUserId]='" & UserId & "'"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)
Return dataSet
End Function
Sub Page_Load(sender As Object, e As EventArgs)
DataGrid1.DataBind()
End Sub
</script>
Thx again for your help.
Reid C.|||ur userid seems to be a number...
so you have to use
Dim queryString As String = "SELECT [VendorEquipment].* FROM [VendorEquipment] WHERE [PropUserId]=" & PropUserId
also one other suggestion...bind your datagrid within if not ispostback...loop
Sub Page_Load(sender As Object, e As EventArgs)
if not ispostback then
DataGrid1.DataBind()
end if
End Sub
HTH
