• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 718
  • Last Modified:

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.resname(restauranttable),3.price(pricetable)
 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..

Open in new window

0
Rajar Ahmed
Asked:
Rajar Ahmed
  • 19
  • 11
1 Solution
 
Gyanendra SinghArchitectCommented:
you need to set datafield name for each column than only you can bind

please refet this link for details
http://www.15seconds.com/issue/040630.htm
0
 
Rajar AhmedConsultantAuthor Commented:
i done with field , but unable to retrieve records in dropdown list in correct way ...
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();
        }
    }

Open in new window

0
 
Rajar AhmedConsultantAuthor Commented:

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

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Rajar AhmedConsultantAuthor Commented:
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


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)
    {
      
 
         
             
     }
}

Open in new window

0
 
Gyanendra SinghArchitectCommented:
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;
0
 
Rajar AhmedConsultantAuthor Commented:


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

Open in new window

0
 
Rajar AhmedConsultantAuthor Commented:
it was null , dint retrieve anything



0
 
Rajar AhmedConsultantAuthor Commented:
hi, cud u plz  suggest to retreive values in dropdown list...

Thanksss....
0
 
Gyanendra SinghArchitectCommented:
for time being remove this
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);
 
            }
 
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
0
 
Rajar AhmedConsultantAuthor Commented:
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...
0
 
Gyanendra SinghArchitectCommented:
you may miss some part of code .. i am giving yoy modified code please try this
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {

       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
0
 
Rajar AhmedConsultantAuthor Commented:
iam jst typing wats happening..
plz wait..


0
 
Rajar AhmedConsultantAuthor Commented:
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 ..





 

 
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();
        }
    }

Open in new window

0
 
Gyanendra SinghArchitectCommented:
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
0
 
Rajar AhmedConsultantAuthor Commented:
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    
0
 
Rajar AhmedConsultantAuthor Commented:
the above is my table structure
0
 
Rajar AhmedConsultantAuthor Commented:
if u ve best approach , ur most welcome , to tell me.....lukin frwd for ur help ..advance thanks...
0
 
Gyanendra SinghArchitectCommented:
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

0
 
Rajar AhmedConsultantAuthor Commented:
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
0
 
Rajar AhmedConsultantAuthor Commented:
it was repeated fid column now ....
how to make it a unique fid which has multiple records in dropdownlist....
0
 
Rajar AhmedConsultantAuthor Commented:
the record value inside the dropdown list , is repeating....
infact it contains several restaurantA  in a single dropdown list ............
0
 
Gyanendra SinghArchitectCommented:
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
                                           
0
 
Rajar AhmedConsultantAuthor Commented:
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,,

0
 
Gyanendra SinghArchitectCommented:
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
0
 
Rajar AhmedConsultantAuthor Commented:
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....
0
 
Gyanendra SinghArchitectCommented:
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    
0
 
Rajar AhmedConsultantAuthor Commented:
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




(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....????

Open in new window

0
 
Gyanendra SinghArchitectCommented:
please find code for that
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
 
        if (e.Row.RowType == DataControlRowType.DataRow)
    {
             int fid = Int32.Parse(e.Row.Cells[0].Text);
String SqlQ = "Select resname from Resttable where RID in ( Selct distinct RID from MAintablec where fild = " + fid;
Datatable mytable= resnameddl1().Tables[0];
     DropDownList ddl = (DropDownList)e.Row.FindControl("dropdownlist1");
   
       ddl.DataSource = mytable;
    ddl.DataTextField = "resname1";
        ddl.DataBind();
    }
    }
  public DataSet resnameddl1(String cmd)
    {
        string constr = ConfigurationManager.AppSettings["constring"];
        SqlConnection myconnection = new SqlConnection(constr);
        DataSet ds = new DataSet();
 
     
        SqlDataAdapter da = new SqlDataAdapter(cmd, myconnection);
        da.Fill(ds, "maintable");
        return ds;
    }

I havent run this code .. but hoping this will work for you
0
 
Rajar AhmedConsultantAuthor Commented:
thanks a lot.....
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 19
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now