How to select data from multiple tables

Question

Hi all I have created two tables as User_tb and consultant_tb in sql server. I just wanna select data from this two tables for the login. Data in those tables are not unique. There is an one login for both users and consultants. When i enter the email and password it should validate from those two tables and redirect to the different pages. Please let me know the Sql Query. Thank you. Table 1- User_tb CREATE TABLE [dbo].[User_tb]( [ID] [ int ] IDENTITY(11) NOT NULL  [U_FirstName] [nvarchar](50) NULL  [U_LasName] [nvarchar](50) NULL  [U_EmailAddress] [nvarchar](50) NOT NULL  [U_Password] [nvarchar](50) NULL  [U_CurrentWeight] [ float ] NULL  [U_CurrentHeight] [ float ] NULL  [U_Gender] [nvarchar](10) NULL  [U_BirthDay] [ date ] NULL  [U_BMI] [ float ] NULL  CONSTRAINT [PK_User_tb] PRIMARY KEY CLUSTERED ( [U_EmailAddress] ASC ) WITH (PAD_INDEX = OFF  STATISTICS_NORECOMPUTE = OFF  IGNORE_DUP_KEY = OFF  ALLOW_ROW_LOCKS = ON  ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ] ) ON [ PRIMARY ] Table 2 - Consultant_tb CREATE TABLE [dbo].[Consultant_tb]( [ID] [ int ] IDENTITY(11) NOT NULL  [C_SurName] [nvarchar](50) NULL  [C_FirstName] [nvarchar](50) NULL  [C_MiddelName] [nvarchar](50) NULL  [C_NIC_Number] [nvarchar](10) NULL  [C_Gender] [nvarchar](10) NULL  [C_Nationality] [nvarchar](50) NULL  [C_Address] [nvarchar](100) NULL  [C_PostalCode] [nvarchar](50) NULL  [C_TelephoneNumber] [ int ] NULL  [C_MobileNumber] [ int ] NULL  [C_MedicalLicenceNumber] [nvarchar](50) NULL  [C_EmailAddress] [nvarchar](50) NOT NULL  [C_Password] [nvarchar](50) NULL  [C_DegreeName] [nvarchar](50) NULL  [C_University] [nvarchar](50) NULL  [C_YearObtained] [ date ] NULL  [C_AdditionalQualification] [nvarchar](500) NULL  [C_Experience] [nvarchar](500) NULL  [C_Evidence] [nvarchar](50) NULL  [C_Advertisement] [nvarchar](50) NULL  CONSTRAINT [PK_Consultant_tb] PRIMARY KEY CLUSTERED ( [C_EmailAddress] ASC ) WITH (PAD_INDEX = OFF  STATISTICS_NORECOMPUTE = OFF  IGNORE_DUP_KEY = OFF  ALLOW_ROW_LOCKS = ON  ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ] ) ON [ PRIMARY ]

Esakki Muthu · Answer

Hi Kasuni Good Day. Do this with stored procedure Please try the below code and let me know. Call this procedure in your code and based on the return result you can either redirect to User page(If procedure returns 1) or Contaltant page (If procedure retunrs 2) or invalid user login error message(If procedure returns 3). CREATE PROCEDURE usp_validateUser @Email nvarchar(50) @ Password nvarchar(50) @IsValidLogin CHAR (1) OUT AS BEGIN IF EXISTS ( SELECT 1 FROM dbo.Consultant_tb WHERE C_EmailAddress = @Email AND C_Password = @ Password ) BEGIN SET @IsValidLogin = '1' ; RETURN END ELSE IF EXISTS( SELECT 1 FROM dbo.User_tb WHERE U_EmailAddress =@Email AND U_Password = @ Password ) BEGIN SET @IsValidLogin = '2' RETURN END ELSE SET @IsValidLogin = '3' RETURN END /* Return 1 - Valid Consultant Retun 2 - Valid User Retun 3 - Not a valid user */

Ashwani Bakshi · Answer

Hi Kasuni You should provide them role and based on it you can redirect them to there respective pages.

Bryian Tan · Answer

I don't see why it neccessary to have several inline select. It look to me that the email and password on both tables are identical. Again that my assumpstion. The code can be simplify to string commandText = select * from User_tb join Consultant_tb on User_tb.ID = Consultant_tb.ID where User_tb.U_EmailAddress = ' + txt_EmailAddress.Text.Trim() + '  + AND User_tb.U_Password = ' + txt_Password.Text.Trim() + ' ; I would suggest to use parameterized queries when possible to prevent SQL injection. https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-2

Ashwani Bakshi · Answer

The Query Is working Fine protected void Button1_Click( object sender EventArgs e) { SqlConnection co = new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings[ con ].ConnectionString); SqlDataAdapter da = new SqlDataAdapter( select * from User_tb join Consultant_tb on User_tb.ID=Consultant_tb.ID where User_tb.U_EmailAddress=(select C_EmailAddress from Consultant_tb where C_EmailAddress=' +TextBox1.Text+ ') and User_tb.U_Password=(select C_Password from Consultant_tb where C_Password=' +TextBox2.Text+ ')  co); DataSet ds = new DataSet(); da.Fill(ds); if (ds.Tables[0].Rows.Count > 0) { Response.Redirect( Home.aspx ); } else Response.Redirect( WebForm1.aspx ); } see your connection you didn't closed it con.close().

Ashwani Bakshi · Answer

hi Kasuni in my code the if condition will execute when row.count is greater then 0.Else it will redirect to WebForm1.aspx (This is the login page in my code).if email password not match then he will be redirected to WebForm1.aspx (login page).

Esakki Muthu · Answer

hey why can't you try with stored procedure  which I mentioned above is there any problem implementing procedure?

ketan hirapara · Answer

yes my bad it's dt DataTable object.

Kasuni Abegunawardana · Answer

Hi Ashwani Bakshi Your code is woring but it redirect only one page. It does't go through the if condition it goes to else redirect page

ketan hirapara · Answer

hi kasuni just copy this and replace your method. protected void btn_login_Click(object sender EventArgs e) { SqlCommand cmd = new SqlCommand(select ut.*ct.* from User_tb ut join Consultant_tb ct on ut.ID =ct.id ct.ID where ut.U_EmailAddress = (select C_EmailAddress from Consultant_tb where C_EmailAddress=' + txt_EmailAddress.Text.Trim() + ') and ut.U_Password= (select C_Password from Consultant_tb where C_Password=' + txt_Password.Text.Trim() + ' con); cmd.Parameters.AddWithValue(@U_EmailAddress txt_EmailAddress.Text); cmd.Parameters.AddWithValue(@U_Password txt_Password.Text); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); string IsConsultant = dr.Rows[0][C_EmailAddress]; sda.Fill(dt); con.Open(); int i = cmd.ExecuteNonQuery(); if (IsConsultant.Length > 0) { Response.Redirect(~/Consultant/Payment.aspx); } else { Response.Redirect(~/Pages/BMI.aspx); } if (chkRememberMe.Checked == true) { Response.Cookies[U_EmailAddress].Value = txt_EmailAddress.Text; Response.Cookies[U_Password].Value = txt_Password.Text; // set cookie expiration in a month Response.Cookies[U_EmailAddress].Expires = DateTime.Now.AddMonths(1); Response.Cookies[U_Password].Expires = DateTime.Now.AddMonths(1); } else { Response.Cookies[U_EmailAddress].Expires = DateTime.Now.AddMonths(-1); Response.Cookies[U_Password].Expires = DateTime.Now.AddMonths(-1); } con.Close(); } Hope it will work for you. Thanks Ketan

Kasuni Abegunawardana · Answer

Hi Ashwani Bakshi Your code is working but the thing is when i'm going to redirect two different pages its only redirect to BMI.aspx. I want to redirect user_tb users into BMI.aspx and consultant_tb users into ~/Consultant/Payment.aspx pages. How can i do that ? protected void btn_login_Click(object sender EventArgs e) { SqlCommand cmd = new SqlCommand(select ut.*ct.* from User_tb ut join Consultant_tb ct on ut.ID =ct.id ct.ID where ut.U_EmailAddress = (select C_EmailAddress from Consultant_tb where C_EmailAddress=' + txt_EmailAddress.Text.Trim() + ') and ut.U_Password= (select C_Password from Consultant_tb where C_Password=' + txt_Password.Text.Trim() + ' con); cmd.Parameters.AddWithValue(@U_EmailAddress txt_EmailAddress.Text); cmd.Parameters.AddWithValue(@U_Password txt_Password.Text); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); sda.Fill(dt); con.Open(); int i = cmd.ExecuteNonQuery(); if (dt.Rows.Count > 0) { if (chkRememberMe.Checked == true) { Response.Cookies[U_EmailAddress].Value = txt_EmailAddress.Text; Response.Cookies[U_Password].Value = txt_Password.Text; // set cookie expiration in a month Response.Cookies[U_EmailAddress].Expires = DateTime.Now.AddMonths(1); Response.Cookies[U_Password].Expires = DateTime.Now.AddMonths(1); } else { Response.Cookies[U_EmailAddress].Expires = DateTime.Now.AddMonths(-1); Response.Cookies[U_Password].Expires = DateTime.Now.AddMonths(-1); } Response.Redirect(~/Pages/BMI.aspx); Session.RemoveAll(); } else { Response.Redirect(~/Consultant/Payment.aspx); //Response.Write( ); //con.Close(); }

ketan hirapara · Answer

hi plz replace this code cmd.CommandText = select * from User_tb join Consultant_tb on User_tb.ID = Consultant_tb.ID where User_tb.U_EmailAddress = (select from Consultant_tb where C_EmailAddress=' + txt_EmailAddress.Text.Trim() + ') and User_tb.U_Password= (select C_Password from Consultant_tb where C_Password=' + txt_Password.Text.Trim() + ' ; by  cmd.CommandText = select ut.*ct.* from User_tb ut join Consultant_tb ct on ut.ID =ct.id ct.ID where ut.U_EmailAddress = (select C_EmailAddress from Consultant_tb where C_EmailAddress=' + txt_EmailAddress.Text.Trim() + ') and ut.U_Password= (select C_Password from Consultant_tb where C_Password=' + txt_Password.Text.Trim() + ' ;

Kasuni Abegunawardana · Answer

It dosen't work for me i got and exception like this. This is the my code. try { con.Open(); cmd.CommandText = select * from User_tb join Consultant_tb on User_tb.ID = Consultant_tb.ID where User_tb.U_EmailAddress = (select from Consultant_tb where C_EmailAddress=' + txt_EmailAddress.Text.Trim() + ') and User_tb.U_Password= (select C_Password from Consultant_tb where C_Password=' + txt_Password.Text.Trim() + ' ; cmd.Connection = con; apd.SelectCommand = cmd; apd.Fill(ds User_tb); if (ds.Tables[0].Rows.Count > 0) { if (txt_EmailAddress.Text.Trim() == ) { if (txt_Password.Text.Trim() == ) { if (chkRememberMe.Checked) { Response.Cookies[U_EmailAddress].Expires = DateTime.Now.AddDays(30); Response.Cookies[U_Password].Expires = DateTime.Now.AddDays(30); } else { Response.Cookies[U_EmailAddress].Expires = DateTime.Now.AddDays(-1); Response.Cookies[U_Password].Expires = DateTime.Now.AddDays(-1); } } else { Response.Write( ); } } else { Response.Redirect(~/BMI.aspx); } Response.Cookies[U_EmailAddress].Value = txt_EmailAddress.Text.Trim(); Response.Cookies[U_Password].Value = txt_Password.Text.Trim(); } } catch (Exception ex) { Response.Write(ex); } System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'from'. Incorrect syntax near '415263'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception Boolean breakConnection Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception Boolean breakConnection Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj Boolean callerHasConnectionLock Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior SqlCommand cmdHandler SqlDataReader dataStream BulkCopySimpleResultSet bulkCopyHandler TdsParserStateObject stateObj Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds RunBehavior runBehavior String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior RunBehavior runBehavior Boolean returnStream Boolean async Int32 timeout Task& task Boolean asyncWrite SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior RunBehavior runBehavior Boolean returnStream String method TaskCompletionSource`1 completion Int32 timeout Task& task Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior RunBehavior runBehavior Boolean returnStream String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset DataTable[] datatables Int32 startRecord Int32 maxRecords String srcTable IDbCommand command CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet Int32 startRecord Int32 maxRecords String srcTable IDbCommand command CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet String srcTable) at Food_Calorie_Calculator__Final_Project.Account.Login.user_2() in c:\Users\A\Desktop\Food new one\Food Calorie Calculator- Final Project\Food Calorie Calculator- Final Project\Account\Login.aspx.cs:line 95 ClientConnectionId:df169bfc-0e87-478e-b8fc-711dc220487a Error Number:156State:1Class:15

Ashwani Bakshi · Answer

Hi Kasuni You should use join for fetching data from both table on login. select * from User_tb join Consultant_tb on User_tb.ID=Consultant_tb.ID where User_tb.U_EmailAddress=(select C_EmailAddress from Consultant_tb where C_EmailAddress=' ') and User_tb.U_Password=(select C_Password from Consultant_tb where C_Password=' ')