how to get data from 3 different tables in sql.

Question

hi, plz tell me how to get data from 3 different tables in sql. in this tables user id is unique,plz help me how to write the store procedure. thanx

Nilanka Dharmadasa · Answer

Hi Roy, It can happen due to one of the following reason. 1. Data set returned by the database is null. So report shows the dataset which was originally set at the creation of the report. 2. Dataset returned by database is not null. But this dataset doesnot support the report. To trouble shoot this you can do the following. For the first one , you can check it by debugging. If you don't know to debug, you can check it by adding this code block after getting datatble. DataSet ds = new DataSet (); SqlDataAdapter adapter = new SqlDataAdapter(cmd ); adapter.Fill(ds); DataTable dt = ds.Tables[0]; //dt contains your dataset. if (dt == null ) MessageBox .Show( "Data set is null" ); If you are sure that data table is not null, then the problem is in your crystal report. If you pass your datatable from Sql server database, you must create your report in a way to suport Sql server database. When you create the connection for report you should do it this way. Create new connection > Select 'OLE DB(ADO)' > Select 'Microsoft Ole DB provider for SQL Server' > Then click next >Then give the server name, user name and password. (These details should match with the details you gave in the connection string used in the code.) Then click finish. Try this and let me know the outcome.

jim · Answer

If you need an example of how to join tables using SQL try this

roy himanshu · Answer

as ur instructions i changed my code,but its again same problem,its shows all the records.how to solve my problem,plz

Nilanka Dharmadasa · Answer

Hi Roy, I have mistakenly typed oRpt.SetDataSource(dt); It should be corrected as follows, cryrpt.SetDataSource(dt); So the final answer is, private void Form1_Load( object sender, EventArgs e) { Bom_Master_Update oj = new Bom_Master_Update (); SqlConnection cn = new SqlConnection (System.Configuration. ConfigurationManager .AppSettings[ "con" ]); cn.Open(); SqlCommand cmd = new SqlCommand ( "report1" , cn); cmd.CommandType = CommandType .StoredProcedure; cmd.Parameters.AddWithValue( "@column002" , oj.comboBox1.Text); //A data table is not returned by executenonquery method. So do it like this. DataSet ds = new DataSet (); SqlDataAdapter adapter = new SqlDataAdapter( cmd ); adapter.Fill(ds); DataTable dt = ds.Tables[0]; //dt contains your dataset. cn.Close(); ReportDocument cryrpt = new ReportDocument (); cryrpt.Load( @"C:\Documents and Settings\Administrator\Desktop\COSTING cryst\costing\CrystalReport1.rpt" ); cryrpt.DataSourceConnections.Clear(); cryrpt.SetDataSource(dt); crystalReportViewer1.ReportSource = cryrpt; crystalReportViewer1.Refresh(); } Store procedure ALTER PROCEDURE report1(@column002 varchar (30)) AS BEGIN select * from CATABLE003 where column001=( select column001 from CATABLE002 where column002=@column002) END In button click private void Report_Click( object sender, EventArgs e) { Form1 obj = new Form1 (); obj.Show(); } If my answer helps you please accept my answer.

Nilanka Dharmadasa · Answer

Hi Roy, Change your code as follows. private void Form1_Load( object sender, EventArgs e) { Bom_Master_Update oj = new Bom_Master_Update (); SqlConnection cn = new SqlConnection (System.Configuration. ConfigurationManager .AppSettings[ "con" ]); cn.Open(); SqlCommand cmd = new SqlCommand ( "report1" , cn); cmd.CommandType = CommandType .StoredProcedure; cmd.Parameters.AddWithValue( "@column002" , oj.comboBox1.Text); //A data table is not returned by executenonquery method. So do it like this. DataSet ds = new DataSet (); SqlDataAdapter adapter = new SqlDataAdapter( cmd ); adapter.Fill(ds); DataTable dt = ds.Tables[0]; //dt contains your dataset. cn.Close(); ReportDocument cryrpt = new ReportDocument (); cryrpt.Load( @"C:\Documents and Settings\Administrator\Desktop\COSTING cryst\costing\CrystalReport1.rpt" ); cryrpt.DataSourceConnections.Clear(); oRpt.SetDataSource(dt); crystalReportViewer1.ReportSource = cryrpt; crystalReportViewer1.Refresh(); } Store procedure ALTER PROCEDURE report1(@column002 varchar (30)) AS BEGIN select * from CATABLE003 where column001=( select column001 from CATABLE002 where column002=@column002) END In button click private void Report_Click( object sender, EventArgs e) { Form1 obj = new Form1 (); obj.Show(); } If my answer helps you please accept my answer.

roy himanshu · Answer

thanx for ur reply but i got all records in report view.but i want records of selected item in combobox.my code is past below.plz help me private void Form1_Load( object sender, EventArgs e) { Bom_Master_Update oj = new Bom_Master_Update (); SqlConnection cn = new SqlConnection (System.Configuration. ConfigurationManager .AppSettings[ "con" ]); cn.Open(); SqlCommand cmd = new SqlCommand ( "report1" , cn); cmd.CommandType = CommandType .StoredProcedure; cmd.Parameters.AddWithValue( "@column002" , oj.comboBox1.Text); cmd.ExecuteNonQuery(); cn.Close(); ReportDocument cryrpt = new ReportDocument (); cryrpt.Load( @"C:\Documents and Settings\Administrator\Desktop\COSTING cryst\costing\CrystalReport1.rpt" ); crystalReportViewer1.ReportSource = cryrpt; crystalReportViewer1.Refresh(); } Store procedure ALTER PROCEDURE report1(@column002 varchar (30)) AS BEGIN select * from CATABLE003 where column001=( select column001 from CATABLE002 where column002=@column002) END In button click private void Report_Click( object sender, EventArgs e) { Form1 obj = new Form1 (); obj.Show(); }

Nilanka Dharmadasa · Answer

Hi Roy, This is the code you should write under your button click event. private void button1_Click( object sender, EventArgs e) { int id = Convert .ToInt32(comboBox1.SelectedValue); //Now you are going to pass the ID to stored procedure. Here, I assume the you are using SQL Server as your DB. SqlConnection sqlConn = null ; try { sqlConn = new SqlConnection(CONNECTION_STRING); //connction string for your DB sqlConn.Open(); SqlCommand sqlCommand = new SqlCommand( "mysp" , sqlConn); // mysp is the name of the stored procedure. sqlCommand.CommandType = CommandType .StoredProcedure; sqlCommand.Parameters.Add( new SqlParameter( "@id" , id)); DataSet ds = new DataSet (); SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand); adapter.Fill(ds); DataTable dt = ds.Tables[0]; //dt contains your dataset. } catch ( Exception ex) { //Error } } Stored Procedure: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go CREATE PROCEDURE [YOUR_SCHEMA] . [ mysp ] @id INT AS BEGIN SELECT * FROM ffff a , qqqq b , rrrr c WHERE a. column001 = b. column001 AND a . column001 = c . column001 AND b. column001 = c . column001 END This is for SQL Server database. If my answer helps you, please accept my answer. :)

naura pax · Answer

himanshu, your combobox must contain one of the unique identifier as ValueMember. on buttonclick you can get selected items unique id. int id=Conver.ToInt32(cmb.SelectedValue); now you'll have to paas it into your stored procedure as variable @id-: Select column1.... from aa inner join qq.... where aa.id=@id in your Stored Procedure. For details of how to create stored procedures please go through following link http://msdn.microsoft.com/en-us/library/ms190669.aspx and on how to call them in code you'll need to look for ADO.net on books online.

Sreekanth · Answer

hi , use:- union all let a,b,c are three tables then select* from a union all select * from b union all select * from c where column name='SREEKANTH' order by "some cloumn name" ...remebr * shuld be equl count and same type... and ur order by sholud be in the last row... http://www.w3schools.com/sql/sql_union.asp

roy himanshu · Answer

Hi, This is my 3 Tables First Table=ffff column001,column002,column003,column004 Second Table=gggg column001,column002,column003,column004 Third Table=rrrr column001,column002,column003,column004 in this tables column001 is unique identifier,my requirement is when i select one item in combobox after click a button then the 3 tables data is displayed ,how to write store procedure for my requirement plz help me thanx

roy himanshu · Answer

Hi, This is my 3 Tables First Table=ffff column001,column002,column003,column004 Second Table=gggg column001,column002,column003,column004 Third Table=rrrr column001,column002,column003,column004 in this tables column001 is unique identifier,my requirement is when i select one item in combobox after click a button then the 3 tables data is displayed ,how to write store procedure for my requirement plz help me thanx

Nilanka Dharmadasa · Answer

Hi Roy, This is the most simple query that we can write to get data from 3 tables named 'tableA', 'tableB' and 'tableC'. I have assumed that all three tables have a column named userid. What is the database that you use? Because the syntax of the stored procedure may vary slightly, depending upon the database. Ex: Oracle, SQL server If you can explin your requirement clearly, I can give you the xact query. If my answer helps you, please accept my answer.

naura pax · Answer

hi, suppose you have three tables A Aid , name B Aid, Bid, bname C Bid, Cid,cname we can write a query Select A.Aid,A.name,B.Bid,B.bname,C.Cid,C.cname from A inner join B on A.Aid=B.Aid inner join C on B.Bid=C.Bid is one example but if you give your table structure you can get better response. bebo.