Solved

Bind Dropdownlist

Posted on 2008-10-29
30
706 Views
Last Modified: 2013-12-17
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
Comment
Question by:Rajar Ahmed
  • 19
  • 11
30 Comments
 
LVL 16

Expert Comment

by:Gyanendra Singh
ID: 22838054
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
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22838333
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
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22838389

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
 
LVL 16

Expert Comment

by:Gyanendra Singh
ID: 22838458
0
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22838482
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
 
LVL 16

Expert Comment

by:Gyanendra Singh
ID: 22838502
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
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22838518


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
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22838563
it was null , dint retrieve anything



0
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22838712
hi, cud u plz  suggest to retreive values in dropdown list...

Thanksss....
0
 
LVL 16

Expert Comment

by:Gyanendra Singh
ID: 22838746
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
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22838773
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
 
LVL 16

Expert Comment

by:Gyanendra Singh
ID: 22839252
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
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22839308
iam jst typing wats happening..
plz wait..


0
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22839351
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
 
LVL 16

Expert Comment

by:Gyanendra Singh
ID: 22839820
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22839841
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
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22839849
the above is my table structure
0
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22840160
if u ve best approach , ur most welcome , to tell me.....lukin frwd for ur help ..advance thanks...
0
 
LVL 16

Expert Comment

by:Gyanendra Singh
ID: 22840247
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
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22840495
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
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22840534
it was repeated fid column now ....
how to make it a unique fid which has multiple records in dropdownlist....
0
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22840704
the record value inside the dropdown list , is repeating....
infact it contains several restaurantA  in a single dropdown list ............
0
 
LVL 16

Expert Comment

by:Gyanendra Singh
ID: 22841304
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
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22841399
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
 
LVL 16

Expert Comment

by:Gyanendra Singh
ID: 22843435
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
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22847520
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
 
LVL 16

Expert Comment

by:Gyanendra Singh
ID: 22847630
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
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22847659
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
 
LVL 16

Accepted Solution

by:
Gyanendra Singh earned 500 total points
ID: 22850247
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
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22850300
thanks a lot.....
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now