QUERY BY USER

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