Showing data from a table depending on certain user

Question

I am new to C# and ASP.net so I am having difficulties in understanding how this scenerio would be possible. I am using Visual Studio 2015. I have an 'owner' log in using their email and password they are redirected to a page which would display a different table called 'Houses' but only display the houses that have the id of the owner which has logged in? I have created the log in and it is able to redirect to the page however I do not know how to show the 'Houses' table that only shows the details depending on the id that is logged in. I have a Houses table which has address number of tenants  vacant and owner id and I would like to show address number of tenants and if it is vacant or not. If there are any online tutorials to help me with this I would be much appreciated. Thanks

Ankit Sharma · Answer

Hi since you are already using Session[LandlordLogin] = txtLandlordEmail.Text; so use checkEmail= Session[LandlordLogin] I guess this will resolve your issue.

Ankit Sharma · Answer

Hi I am glad that I can be of your help. Please accept the helpful solution.

Ankit Sharma · Answer

Hi Michelle Please read the error message carefully. It clearly stats :- 'Invalid column name landlord_Email' . check the correct column name in your table. Request you to do some dubugging from your end before posting your questions as i am not aware of your whole project but only a part of it. Read the error messages carefully and proceed accordingly. Try using breakpoints to check if your variables are getting correct value or not.

Michelle Carthy · Answer

I have solved this problem it was in my SQL. Thank you it is working now. You were great help. Thanks Michelle

Michelle Carthy · Answer

Unfortunately there is an error coming from using this. I placed checkEmail= Session[LandlordLogin] inside the page load before the if statements i am not sure whether this was correct or not. And this error is showing when i attempt to log in now.

Michelle Carthy · Answer

So i have declared 'string checkEmail=String.Empty' in my landlord public class.. however where would I assign 'checkEmail= txtLandlordEmail.Text' ? When I attempt putting it into my landlord page an error occurs saying 'the name txtLandlordEmail does not exist in the currrent context' which is understandable as I do not have a textbox on this page so I am confused where to assign this variable ?

Ankit Sharma · Answer

yes you need to declare and assign a value to varibale checkEmail Declare :- string checkEmail=String.Empty Assign :- checkEmail= txtLandlordEmail.Text

Michelle Carthy · Answer

Apologies that did not show what the error had said. it says the name 'checkEmail' does not exist in the current context

Michelle Carthy · Answer

This is the error that is showing. I am unsure should i declare this variable checkEmail on this page too?

Ankit Sharma · Answer

Please explain your error. Where and what error you are getting. Try using breakpoints to debug your application. you can locate and solve error easily.

Michelle Carthy · Answer

Hi I already have this code under my login button and used a string variable called checkEmail however when I used this in my sql query it showed an error. protected void Button_Login_Click(object sender EventArgs e) { SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[rent-dbConnectionString1].ConnectionString); conn.Open(); string checkEmail = select count(*) FROM Landlords where Landlord_Email=' + txtLandlordEmail.Text + '; SqlCommand com = new SqlCommand(checkEmail conn); int temp = Convert.ToInt32(com.ExecuteScalar().ToString()); conn.Close(); if (temp == 1) { conn.Open(); string checkPassword = select Landlord_Password from Landlords where Landlord_Email=' + txtLandlordEmail.Text + '; SqlCommand passcom = new SqlCommand(checkPassword conn); string Password = passcom.ExecuteScalar().ToString(); if (Password == txtLandlordPassword.Text) { Session[LandlordLogin] = txtLandlordEmail.Text; Response.Write(Password is correct); Response.Redirect(LandlordIndex.aspx); } else { Response.Write(Password is incorrect); } } else { Response.Write(Landlord Email is incorrect); } }

Ankit Sharma · Answer

Hi  While login capture the login email in a string variable say testemail. then use this comm.CommandText = select p.Property_Address p.Property_Num_Of_Tenants p.Property_Vacant from Properties p inner join Landlords l On p.Landlord_Id = l.Landlord_Id where l.Landlord_Id = p.Landlord_Id and p.Landlord_Email= +testemail; Try and let me know if this works.

Michelle Carthy · Answer

Hi Ankit Yes no prob. In my Landlords table I currently have Landlord_Id Landlord_Name Landlord_Email Landlord_TelNum Landlord_Password and in my Properties table i currently have Property_Id (PK) Property_AddressProperty_Num_Of_Tenants Landlord_Id (FK) so when a certain landlord logs into their account they can view the properties that have their id ? Not sure how to include that in my SQL query from the last response. Thanks Michelle

Ankit Sharma · Answer

Hi Could you please provide table structure for landlord and properties table  so that i can find any relationship b/w them. For join to work there must be some common column in both table.

Michelle Carthy · Answer

Hi Ankit Thank you for your feedback. I have changed the tables names to landlord and properties (just so it doesnt cause confusion). I have successfull created a dynamic HTML table of the properties using this code. like so: SqlConnection con = new SqlConnection(); con.ConnectionString = ConfigurationManager.ConnectionStrings[rent-dbConnectionString1].ToString(); con.Open(); SqlCommand comm = new SqlCommand(); comm.CommandText = select p.Property_Address p.Property_Num_Of_Tenants p.Property_Vacant from Properties p inner join Landlords l On p.Landlord_Id = l.Landlord_Id where l.Landlord_Id = 1; comm.Connection = con; SqlDataReader rd = comm.ExecuteReader(); table.Append( ); table.Append( Address ); table.Append( Number of Tenants ); table.Append( Vacant? ); table.Append( ); etc... However I would like to change the where l.Landlord_Id = 1  to basically say where l.Landlord_Id = 'the id of the landlord that has logged in'  My user logs in with an email and password. Hopefully you can help me with this Thanks Michelle

Ankit Sharma · Answer

Hi Michelle you need to join both tables and fetch data from 'Houses' tabel based on owner id of logged in user then you can bind this data to your webpage. you can try using this query with some modifications select hs.* from Houses hs inner join owner ow on ow.ownerid=hs.ownerid Learn about SQL join here https://www.w3schools.com/sql/sql_join.asp http://www.dofactory.com/sql/join Hope this helps

Ramesh Palanivel · Answer

Hi Michelle I am not clear with your question. Are you looking role based document hide and show? Please refer the URL You will get some idea about it. https://www.aspsnippets.com/Articles/Implement-Role-based-security-Page-access-and-ShowHide-Menu-items-based-on-Role-in-ASPNet.aspx

Showing data from a table depending on certain user,