Bind Dropdownlist
I need to bind dropdown list from db in gridview using foreign key .
i ve three tables
1.foodtable field
fid int(identity(1,1),primary)
fname varchar
table 2:Restauranttable
1.Rid primary key
2. restaurantname varchar
table3 : pricetable
1.pid int(identity(1,1),primary)
2.fid int foriegn key(foodtable)
3..rid int foreign key(resttable)
4.price float
My gridview has 3 columns
1.Fid(pricetable).2.resnam
please : in brakets r the table am relating .
Gridview explanation
1.fid is from pricetable
2.important field : restaurantname this is a drop down list field , i need to bind from database from restauranttable usning foreign key rid of pricetable ,which ll depends on fid .
3.Price, this field should change according to the restaurant selection in the dropdown list.
I tried a lots n lots of code . but neither gave me a result , so plz sort out the result for me...
i ve three tables
1.foodtable field
fid int(identity(1,1),primary)
fname varchar
table 2:Restauranttable
1.Rid primary key
2. restaurantname varchar
table3 : pricetable
1.pid int(identity(1,1),primary)
2.fid int foriegn key(foodtable)
3..rid int foreign key(resttable)
4.price float
My gridview has 3 columns
1.Fid(pricetable).2.resnam
please : in brakets r the table am relating .
Gridview explanation
1.fid is from pricetable
2.important field : restaurantname this is a drop down list field , i need to bind from database from restauranttable usning foreign key rid of pricetable ,which ll depends on fid .
3.Price, this field should change according to the restaurant selection in the dropdown list.
I tried a lots n lots of code . but neither gave me a result , so plz sort out the result for me...
<div><asp:GridView ID="GridView1" runat="server" style="left: 157px; position: relative; top: 141px" OnRowDataBound="GridView1_RowDataBound" AutoGenerateColumns="False">
<Columns>
<asp:BoundField HeaderText="fid" />
<asp:BoundField HeaderText="fname" />
<asp:TemplateField HeaderText="resname">
<ItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server" Style="position: relative" AutoPostBack="true" DataTextField="resname" DataValueField="" >
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField HeaderText="price" />
</Columns>
</asp:GridView>
I have the sever side code , since its not am workin properly am not pasting here..
ASKER
i done with field , but unable to retrieve records in dropdown list in correct way ...
wondering y ..
wondering y ..
<div><asp:GridView ID="GridView1" runat="server" style="left: 157px; position: relative; top: 141px" OnRowDataBound="GridView1_RowDataBound" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="fid" HeaderText="fid" />
<asp:BoundField HeaderText="Fname" DataField="fname"/>
<asp:TemplateField HeaderText="rname">
<ItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server" Style="position: relative" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField HeaderText="price" />
<asp:TemplateField HeaderText="Quantity">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" Style="position: relative"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:ButtonField CommandName="addcart" HeaderText="ADDCART" Text="Button" />
</Columns>
</asp:GridView>
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
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 fname from foodtable where fid=maintable.fid) as fname,dbo.resname1(fid) 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;
string temp = null;
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();
string myString = row["resname"].ToString();
string[] split = myString.Split(',');
//Response.Write(split.Length);
for (int k = 0; k < split.Length; k++)
{
//ddl.Items.Add(split[k]);
//Response.Write(split[k] +"<br>");
temp = temp + split[k];
newrow["resname1"] = split[k];
}
newrow["rid"] = row["fid"];
newrow["resname1"] = temp;
mytable.Rows.Add(newrow);
ddl.DataSource = mytable;
//for (int k = 0; k < split.Length; k++)
// ddl.Items.Add(split[k]);
}
//ddl.DataSource = mytable;
//for (int k = 0; k < split.Length; k++)
ddl.DataTextField = "resname1";
ddl.DataValueField = "rid";
ddl.DataBind();
}
}ASKER
I used two queries to bind dropdownlist neither gave me a result
1.select distinct fid,(select fname from foodtable where fid=maintable.fid) as fname, (select resname from restauranttable where rid=maintable.rid)as resname from maintable
2.select distinct fid,(select fname from foodtable where fid=maintable.fid) as fname,dbo.resname1(fid) as resname from maintable
dbo.resname is a fuction
ASKER
hi , i
used this code only to execute
u can check with my below code...
Just as same...
infact i can able to retrive the restaurant name,
but the thing is
am getting in dropdown list , all the records in the same index itself
used this code only to execute
u can check with my below code...
Just as same...
infact i can able to retrive the restaurant name,
but the thing is
am getting in dropdown list , all the records in the same index itself
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class griduser : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
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 fname from foodtable where fid=maintable.fid) as fname,dbo.resname(fid) 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();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
Response.Redirect("maintable.aspx");
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
}
}
are you sure below line is working fine
newrow["rid"] = row["fid"];
newrow["resname1"] = row["resname"];
please put breakpoint in this line and check the value of datatabe
ddl.DataSource = mytable;
newrow["rid"] = row["fid"];
newrow["resname1"] = row["resname"];
please put breakpoint in this line and check the value of datatabe
ddl.DataSource = mytable;
ASKER
in dropdown list also am getting like this in the same way...
Dropdown list , displays like
all rcords are merged in a single line itself ..
this is the query i used , to bind in dropdown
select dbo.resname1(fid) from maintable
Restaurant C,Restaurant B,Restaurant D,Restaurant A
Restaurant c,Restaurant B
ASKER
it was null , dint retrieve anything
ASKER
hi, cud u plz suggest to retreive values in dropdown list...
Thanksss....
Thanksss....
for time being remove this
fid = Int32.Parse(e.Row.Cells[0]
expression = "fid = " + fid;
DropDownList ddl = (DropDownList)e.Row.FindCo
DataRow[] rows = ds.Tables[0].Select(expres
foreach (DataRow row in rows)
{
DataRow newrow = mytable.NewRow();
newrow["rid"] = row["fid"];
newrow["resname1"] = row["resname"];
mytable.Rows.Add(newrow);
}
and directly bind dataset to your dropdown
DataSet ds = new DataSet();
ds = resnameddl1();
ddl.DataSource = ds.Tables[0];
ddl.DataTextField = "resname1"; // Please provide correct display
ddl.DataValueField = "rid"; // change this also
ddl.DataBind();
and test
fid = Int32.Parse(e.Row.Cells[0]
expression = "fid = " + fid;
DropDownList ddl = (DropDownList)e.Row.FindCo
DataRow[] rows = ds.Tables[0].Select(expres
foreach (DataRow row in rows)
{
DataRow newrow = mytable.NewRow();
newrow["rid"] = row["fid"];
newrow["resname1"] = row["resname"];
mytable.Rows.Add(newrow);
}
and directly bind dataset to your dropdown
DataSet ds = new DataSet();
ds = resnameddl1();
ddl.DataSource = ds.Tables[0];
ddl.DataTextField = "resname1"; // Please provide correct display
ddl.DataValueField = "rid"; // change this also
ddl.DataBind();
and test
ASKER
on the currect changes as ur idea , it raised the following error,
Object reference not set to an instance of an object.
i had given the correct display also..
Thanks...
Object reference not set to an instance of an object.
i had given the correct display also..
Thanks...
you may miss some part of code .. i am giving yoy modified code please try this
protected void GridView1_RowDataBound(obj
{
DataSet ds = new DataSet();
ds = resnameddl1();
if (e.Row.RowType == DataControlRowType.DataRow
{
ddl.DataSource = ds.Tables[0] ;
//ddl.DataTextField = "resname1"; -> here you put field name
// ddl.DataValueField = "rid";-> here you put field name
ddl.DataBind();
}
}
Please remember you need to change the value of this field
ddl.DataTextField
ddl.DataValueField
and let me know result .. if still you are getting problem please paste details of issue here
protected void GridView1_RowDataBound(obj
{
DataSet ds = new DataSet();
ds = resnameddl1();
if (e.Row.RowType == DataControlRowType.DataRow
{
ddl.DataSource = ds.Tables[0] ;
//ddl.DataTextField = "resname1"; -> here you put field name
// ddl.DataValueField = "rid";-> here you put field name
ddl.DataBind();
}
}
Please remember you need to change the value of this field
ddl.DataTextField
ddl.DataValueField
and let me know result .. if still you are getting problem please paste details of issue here
ASKER
iam jst typing wats happening..
plz wait..
plz wait..
ASKER
actually , when i used ur code ,
its retriving all the records of resname in the dropdown list..
i want records only withrespect fo fid ..
for eg
if resname has 3 three records resname1,resname2,resname3
I want like this
fid resname
1 resname1,resname2
2 resname1
But the o/p was
fid resname
1 resname1,resname2,resname3
2 resname1,resname2,resname3
3 resname1,resname2,resname3
and the other "old" problem was
Dropdown list displaying
1. Dropdown was again displayin all the records was filled in a single index itself...
when i put break point
1.ddl value was returning null.... although it display that improper value in dropdown list..
Below i pasted my codin which i did....Plzz help ..
its retriving all the records of resname in the dropdown list..
i want records only withrespect fo fid ..
for eg
if resname has 3 three records resname1,resname2,resname3
I want like this
fid resname
1 resname1,resname2
2 resname1
But the o/p was
fid resname
1 resname1,resname2,resname3
2 resname1,resname2,resname3
3 resname1,resname2,resname3
and the other "old" problem was
Dropdown list displaying
1. Dropdown was again displayin all the records was filled in a single index itself...
when i put break point
1.ddl value was returning null.... although it display that improper value in dropdown list..
Below i pasted my codin which i did....Plzz help ..
public partial class griduser : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
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 fname from foodtable where fid=maintable.fid) as fname,dbo.resname(fid) 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;
// DropDownList ddl = (DropDownList)e.Row.FindControl("dropdownlist1");
// ddl.DataSource = ds;
// ddl.DataTextField = "resname"; // Please provide correct display
// ddl.DataValueField = "rid"; // change this also
// ddl.DataBind();
// //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();
// //}
//}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
DataSet ds = new DataSet();
ds = resnameddl1();
if (e.Row.RowType == DataControlRowType.DataRow)
{
DropDownList ddl = (DropDownList)e.Row.FindControl("dropdownlist1");
ddl.DataSource = ds.Tables[0];
ddl.DataTextField = "resname";
ddl.DataValueField = "fid";
ddl.DataBind();
}
}
means your this part of code is not correct
resnameddl1()
you need to redisgn your query ... I dont know the structure of table that is why right now help less...
can you please share your table structure
resnameddl1()
you need to redisgn your query ... I dont know the structure of table that is why right now help less...
can you please share your table structure
ASKER
my table structure
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
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
ASKER
the above is my table structure
ASKER
if u ve best approach , ur most welcome , to tell me.....lukin frwd for ur help ..advance thanks...
test this query
Select a.fid,a.fname, b.resname ,c.price From foodtable a, Resttable b,MAintablec Where
a.fid = c.fid and b.rid = c.rid
Select a.fid,a.fname, b.resname ,c.price From foodtable a, Resttable b,MAintablec Where
a.fid = c.fid and b.rid = c.rid
ASKER
result is
56 dosai Restaurant B 21.0
63 Idiappam Restaurant C 40.0
56 dosai Restaurant C 21.0
56 dosai Restaurant D 21.0
63 Idiappam Restaurant A 45.0
56 dosai Restaurant A 23.0
56 dosai Restaurant B 21.0
63 Idiappam Restaurant C 40.0
56 dosai Restaurant C 21.0
56 dosai Restaurant D 21.0
63 Idiappam Restaurant A 45.0
56 dosai Restaurant A 23.0
ASKER
it was repeated fid column now ....
how to make it a unique fid which has multiple records in dropdownlist....
how to make it a unique fid which has multiple records in dropdownlist....
ASKER
the record value inside the dropdown list , is repeating....
infact it contains several restaurantA in a single dropdown list ............
infact it contains several restaurantA in a single dropdown list ............
i guess you need to change your approach ... you only get Restaurant name and ID ... from this table
table 2:Resttable so RIgtD would be yr primary key and restable would be display .. and based on user selection you can get what you want got my point
sql Query : select Distinct Rid,resname from Resttable
.. simple
table 2:Resttable so RIgtD would be yr primary key and restable would be display .. and based on user selection you can get what you want got my point
sql Query : select Distinct Rid,resname from Resttable
.. simple
ASKER
This is d query am using r8 now,which u mentioned
Select a.fid,a.fname, b.resname ,c.price From foodtable a, Resttable b,MAintablec Wherea.fid = c.fid and b.rid = c.rid
which is repeatin fid values in datagrid , where i need only one unique fid but multiple restaurant names in dropdownlist,
If i need to change the query as u mention now , where shall i put, but also i din get properly wat u said,jst a begginer kindly tell me....
Thanks for helpin so, far,,
Select a.fid,a.fname, b.resname ,c.price From foodtable a, Resttable b,MAintablec Wherea.fid = c.fid and b.rid = c.rid
which is repeatin fid values in datagrid , where i need only one unique fid but multiple restaurant names in dropdownlist,
If i need to change the query as u mention now , where shall i put, but also i din get properly wat u said,jst a begginer kindly tell me....
Thanks for helpin so, far,,
ok let me explain ... u
1 use this query
select Distinct Rid,resname from Resttable
so on your dropdown item will display like
RID - RestNAme
1 - Restaurant A
2. Restaurant B
3. Restaurant C
4. Restaurant D
2. Now user select Restaurant C from dropdown .. then you will get RID value and that is 3 for C.. Right
3. now with the help of RID you can get FID from MAintablec ...
i.e Select FID from MAintablec where RID = value which you get from dropdown
4. now you know FID .. so you can query to foodtable and get fname
got my point
1 use this query
select Distinct Rid,resname from Resttable
so on your dropdown item will display like
RID - RestNAme
1 - Restaurant A
2. Restaurant B
3. Restaurant C
4. Restaurant D
2. Now user select Restaurant C from dropdown .. then you will get RID value and that is 3 for C.. Right
3. now with the help of RID you can get FID from MAintablec ...
i.e Select FID from MAintablec where RID = value which you get from dropdown
4. now you know FID .. so you can query to foodtable and get fname
got my point
ASKER
hi bondinasp,
I think i made a teriffic mistake somever , thats y, u dint understand my problem so far,sorryyyyy...
I want fid indexx to get resname , not using rid index to get fname . getiin fname its not been a problm for me .. Wat my problm .. according to unique fid index values of maintable i need to fill up the rid of maintable which is in dropdown list .................am i clear atleat now....... ???
I ll definetly award points for helpin so far,
Thanks....
I think i made a teriffic mistake somever , thats y, u dint understand my problem so far,sorryyyyy...
I want fid indexx to get resname , not using rid index to get fname . getiin fname its not been a problm for me .. Wat my problm .. according to unique fid index values of maintable i need to fill up the rid of maintable which is in dropdown list .................am i clear atleat now....... ???
I ll definetly award points for helpin so far,
Thanks....
see than you need tu put 2 drop down .. .
1. first will contain fname and based on selection of this
2. Fill rest name ( 2nd dropdown )
so 1 dropdown will display
56 dosai
63 Idiappam
id user select dosai ... than based on this selection
populate 2nd dropdown
Restaurant B
Restaurant C
Restaurant D
Restaurant A
1. first will contain fname and based on selection of this
2. Fill rest name ( 2nd dropdown )
so 1 dropdown will display
56 dosai
63 Idiappam
id user select dosai ... than based on this selection
populate 2nd dropdown
Restaurant B
Restaurant C
Restaurant D
Restaurant A
ASKER
hey,
I want user to c , all my foodname individuallly in a column not in dropdown , Where as the user can select any of the restaurants in the dropdown list ...PLZ IF U CAN FORM THE QUERY , LET ME NO.....
mY DOUBT...
Is it possible to use sqldatasource ctrl in that using querybuilder, so that, i can read the grid view cell value of fid to get the resname in dropdown list....
For eg
like this
I want user to c , all my foodname individuallly in a column not in dropdown , Where as the user can select any of the restaurants in the dropdown list ...PLZ IF U CAN FORM THE QUERY , LET ME NO.....
mY DOUBT...
Is it possible to use sqldatasource ctrl in that using querybuilder, so that, i can read the grid view cell value of fid to get the resname in dropdown list....
For eg
like this
(select resname from resttable where rid=rid.maintable) as resname from maintable where fid= row.Cells(0).TexT
iS IT POSSIBLE TO READ CELL VALUE OF GRIDVIEW IN qUERYBUILDER OF SQLDATASOURCECONTROL....????
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks a lot.....
please refet this link for details
http://www.15seconds.com/issue/040630.htm