AspdotnetCodes.com
Search
Articles
Books
Resources
Asp.Net News
Downloads
Free Tech Magazines
Asp.Net Web Hosting
Archives
Softwares
Newsletter
Suggest Us
Link to Us
Feeds Subscription
Articles
Questions & Answers
Tips & Tricks
 Sql query doubt
Posted by rajarahmed on  10/25/2008 6:23:58 AM Category: Asp.Net 2.0
Add to my favorites
Email to friend
Hi ,below is my query where i used , subquery to connect two tables . where fid contains multiples records of resname , so, i used this query to display ,,



select distinct fid,(select resname from resttable where rid = maintable.rid) from maintable

There raised one problem, it created additional row for every resname field , For example fid 1 as four resname records it displaying

fid 1 - resname 1,resname 2, resname3 , resname 4(dropdown list)

fid 1 resname 1,resname 2, resname3 , resname 4 (dropdown list)

fid1 resname 1,resname 2, resname3 , resname 4 (dropdown list)

fid 1 resname 1,resname 2, resname3 , resname 4 (dropdown list)( all r displaying in four different rows)

but i need to execute like , fid 1 having resname 1,resname 2, resname3 , resname 4 .in a single row (which am puttin this in dropdown list and want in simgle row )

and i guess my explanation is clear for u all . .. )

SO, is there anyway to resolve....

below is my code n table structure , kindly help..

table1:foodtable field

fid int(identity(1,1),primary)

fname varchar

table 2:Resttable

1.Rid primary key

2. resname varchar

table3 : MAintable

1.pid int(identity(1,1),primary)
2.fid int foriegn key(foodtable)

3..rid int foreign key(resttable)

4.price float


protected void Page_Load(object sender, EventArgs e)
{
GridView1.DataSource = resnameddl1().Tables[0];
GridView1.DataBind();

}
public DataSet resnameddl1()
{
string constr = ConfigurationManager.AppSettings["constring"];
SqlConnection myconnection = new SqlConnection(constr);
DataSet ds = new DataSet();
string cmd = "select distinct fid,(select distinct fname from foodtable where fid=maintable.fid)as fname,(select resname from resttable where rid=maintable.rid)as resname from maintable";

SqlDataAdapter da = new SqlDataAdapter(cmd, myconnection);

da.Fill(ds, "maintable");
return ds;
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{

DataTable mytable = new DataTable();
DataColumn restid = new DataColumn("rid");
DataColumn restname1 = new DataColumn("resname1");

mytable.Columns.Add(restid);
mytable.Columns.Add(restname1);

DataSet ds = new DataSet();
ds = resnameddl1();
int fid = 0;
string expression = string.Empty;

if (e.Row.RowType == DataControlRowType.DataRow)
{
fid = Int32.Parse(e.Row.Cells[0].Text);
expression = "fid = " + fid;
DropDownList ddl = (DropDownList)e.Row.FindControl("dropdownlist1");
DataRow[] rows = ds.Tables[0].Select(expression);

foreach (DataRow row in rows)
{
DataRow newrow = mytable.NewRow();
newrow["rid"] = row["fid"];
newrow["resname1"] = row["resname"];
mytable.Rows.Add(newrow);

}

ddl.DataSource = mytable;
ddl.DataTextField = "resname1";
ddl.DataValueField = "rid";
ddl.DataBind();
}
}

tell me ny nessesity to change structure of table , and also highlight wthr it is correct, i think i messed up with struc oly ..plz enlight it ....

Thanks Rajar ..

Post Your Reply Post New Question

 Viewer's Reply
try it
Answered By prasath.prakasam on 11/13/2008 6:24:05 AM
select a.fid ,(select b.details from deails b where a.fid=b.fid) from fidtable a
 
Post Your Reply If you still have problem, post it again under this reply for better response.

 
Sponsored by
Web Application Security — How to Minimize Prevalent Risk of Attacks
Speech Technology Magazine
Many .NET teams are underperforming, is yours?