Solved

ASP.net, Sqlserver 2000 dropdown list binding

Posted on 2008-10-28
9
755 Views
Last Modified: 2012-05-05
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    

1.i need to bind resname fireld of restable in dropdownlist from maintable which has a foreign key rid  .
2.""""" importantly""""" : Dropdown list should hold details of resname w.r.to fid of maintable .  
3.if i selected ,resname from dropdown list i need display the price field according using onselectedindex

datagrid Viewimportant fields  :
fid(maintable) ,
 resname(resttable),
 price(maintable)  [ details in brackets refers to the table which i want to bind]
i tried the followin code , but not able to retreive , dunno y . kindly suggest .
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="userinterface.aspx.cs" Inherits="userinterface" %>

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

 

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

    <title>Untitled Page</title>

</head>

<body>

    <form id="form1" runat="server">

        &nbsp;<asp:GridView ID="GridView1" runat="server" style="left: 157px; position: relative; top: 141px" OnRowDataBound="GridView1_RowDataBound">

            <Columns>

                <asp:BoundField DataField="fid" HeaderText="Fname" />

                <asp:TemplateField HeaderText="rname">

                    <EditItemTemplate>

                        <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("resname") %>'></asp:TextBox>

                    </EditItemTemplate>

                    <ItemTemplate>

                        <asp:DropDownList ID="DropDownList1" runat="server" Style="position: relative">

                        </asp:DropDownList>

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:BoundField HeaderText="price" DataField Price/>

            </Columns>

        </asp:GridView>

        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Style="position: relative"

            Text="Button" />

  

  </form>

</body>

</html>

 

 

 

 

Server side Script

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 userinterface : System.Web.UI.Page

{

 

    protected void Page_Load(object sender, EventArgs e)

    {

        GridView1.DataSource = resnameddl().Tables[0];

        GridView1.DataBind();   

 

    }

    public DataSet resnameddl()

       {

        string connectionstring = "Data Source=localhost;Initial Catalog=project;Integrated Security=SSPI";

        DataSet ds = new DataSet();

        string cmd = "select  distinct fid,(select  distinct fname from foodtable where fid=maintable.fid)as fname,stuff( (select ','+resname from resttable where rid = maintable.rid for xml path('')),1,1,'') as resname  from maintable";

        SqlConnection myconnection = new SqlConnection(connectionstring);

        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 productidcolumn = new DataColumn("rid");

        DataColumn productnamecolumn = new DataColumn("resname");

 

        mytable.Columns.Add(productidcolumn);

        mytable.Columns.Add(productnamecolumn);

 

        DataSet ds = new DataSet();

        ds = resnameddl();

        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["resname"] = row["resname"];

                mytable.Rows.Add(newrow);

 

            }

 

            ddl.DataSource = mytable;

            ddl.DataTextField = "resname";

            ddl.DataValueField = "rid";

            ddl.DataBind();

        }

    }

    protected void Button1_Click(object sender, EventArgs e)

    {

        Response.Redirect("maintable.aspx");

    }

}

Open in new window

0
Comment
Question by:Rajar Ahmed
  • 5
  • 4
9 Comments
 
LVL 12

Accepted Solution

by:
techExtreme earned 500 total points
ID: 22819987
Hello, Let me first tell you what I think you want:
1. You want to show restaurant name in dropdown list,
2. Once user selects a restaurant name from the dropdown list,
   you must have access to its "rid" and the "price" of the concerned
   restaurant and the food in the grid right?

Here's the query,
SELECT rt.resname, rt.rid + ';' + mt.price + ';' + mt.fid
FROM Resttable rt inner join Maintable mt
on rt.rid = mt.rid
inner join foodtable ft
on ft.fid = mt.fid


Please not that you are using dropdown, hence you can only show one value
to user and store one value as key, we want to store multiple keys, so
I have created a semi;colon  delimited list of the ids required and the
sql query will pass me two columns first one the restaurant name,
second one a semicolumn seperated list of all ids i need for that rest.

Then you can fire this query and get data for displaying in the grid.
Note if you also want foodname to be there in the dropdown's selected key,
add it to the query like ft.fname  .

Now when you do check selectedvalue you will get something like
101;99.90;56   which as of now corresponds to 101=rest id, 99.90 = price,
56 is food id   (our query for second column was: rt.rid + ';' + mt.price + ';' + mt.fid remember)

Hope it helped.

Enjoy Coding!
0
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22820865
Exactly wat u said is absolutely correct , Thanks
but here comes wat has happend .  
in that query ,  i found this error , Syntax error converting the varchar value ';' to a column of data type int

Below is the eg, how to be done....
my gridview has three columns
fid(bound colomn ), resname(Dropdown) , price(bound column) .

I want to display in dropdown list ,
if ,fid 1 has 3 restaurants therefore , dropdown list should ve Resname1 , resname2,resname3  only .
if, fid 2 has 2 restaurants i, dropdown should ve resname2,resname4 only  .
and then w.r.t to the resname the third column of gridview which has price field which  should change accordingly to resname,


Kindly overview my aspx page  code . which i again pasted below .



<%@ Page Language="C#" AutoEventWireup="true" CodeFile="userinterface.aspx.cs" Inherits="userinterface" %>

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

 

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

    <title>Untitled Page</title>

</head>

<body>

    <form id="form1" runat="server">

        &nbsp;<asp:GridView ID="GridView1" runat="server" style="left: 157px; position: relative; top: 141px" OnRowDataBound="GridView1_RowDataBound">

            <Columns>

                <asp:BoundField DataField="fid" HeaderText="Fname" />

                <asp:TemplateField HeaderText="rname">

                    <EditItemTemplate>

                        <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("resname") %>'></asp:TextBox>

                    </EditItemTemplate>

                    <ItemTemplate>

                        <asp:DropDownList ID="DropDownList1" runat="server" Style="position: relative">

                        </asp:DropDownList>

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:BoundField HeaderText="price" DataField Price/>

            </Columns>

        </asp:GridView>

        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Style="position: relative"

            Text="Button" />

  

  </form>

</body>

</html>

 

 

 

 

Server side Script

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 userinterface : System.Web.UI.Page

{

 

    protected void Page_Load(object sender, EventArgs e)

    {

        GridView1.DataSource = resnameddl().Tables[0];

        GridView1.DataBind();   

 

    }

    public DataSet resnameddl()

       {

        string connectionstring = "Data Source=localhost;Initial Catalog=project;Integrated Security=SSPI";

        DataSet ds = new DataSet();

        string cmd = "select  distinct fid,(select  distinct fname from foodtable where fid=maintable.fid)as fname,stuff( (select ','+resname from resttable where rid = maintable.rid for xml path('')),1,1,'') as resname  from maintable";

        SqlConnection myconnection = new SqlConnection(connectionstring);

        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 productidcolumn = new DataColumn("rid");

        DataColumn productnamecolumn = new DataColumn("resname");

 

        mytable.Columns.Add(productidcolumn);

        mytable.Columns.Add(productnamecolumn);

 

        DataSet ds = new DataSet();

        ds = resnameddl();

        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["resname"] = row["resname"];

                mytable.Rows.Add(newrow);

 

            }

 

            ddl.DataSource = mytable;

            ddl.DataTextField = "resname";

            ddl.DataValueField = "rid";

            ddl.DataBind();

        }

    }

    protected void Button1_Click(object sender, EventArgs e)

    {

        Response.Redirect("maintable.aspx");

    }

}

Open in new window

0
 
LVL 12

Expert Comment

by:techExtreme
ID: 22821083
Hello,
Firstly I hope the query i wrote , you are using it to bind a dropdown list and
not the gridview!

Now as to the casting error, rewrite query as this:

SELECT rt.resname, CAST(rt.rid as varchar) + ';' + cast(mt.price as varchar) + ';' + cast(mt.fid as varchar)
FROM Resttable rt inner join Maintable mt
on rt.rid = mt.rid
inner join foodtable ft
on ft.fid = mt.fid


Also I'm not sure how you are binding the gridview.

Moreover, there's an error in your gridview markup:
you have :
<asp:BoundField HeaderText="price" DataField Price/>
It must be
<asp:BoundField HeaderText="price" DataField="Price"/>
(notice = and quotes after the datafield)
Plz chk if it is so or not.


Also on the selected index change of the dropdown,  you have to split the
semicolon seperated value string as requied and take action on the individual
elements that you get.

Let me know of any issues.
0
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22821247
hi ,in the query analyser it displaying with semicoln  ,which i was wondering that how ll i change into only resname in the ddropdown list according to the fid ..
The result was  
for eg
resname a : 1;23;4
resname b :2,23;9
Like that ..

but in the appln (i mean in browser). it shows error .A field or property with the name 'fid' was not found on the selected data source.

About the grid view datafield price , i changed the content as u said .,  
Plz enlightn...Wat to be done...
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 12

Expert Comment

by:techExtreme
ID: 22821308
Firstly in the query analyzer  you should get two columns  
like
resname 1           1;23;4
not resname 1 : 1;23;4   (i.e. there should not be a colon :  after resname, otherwise you mistook the , to be a : in the query).

If you have the datatextfield of dropdown list set to 'fid', then change the query as shown below. Text shown in bold is new

SELECT rt.resname, CAST(rt.rid as varchar) + ';' + cast(mt.price as varchar) + ';' + cast(mt.fid as varchar) as fid
FROM Resttable rt inner join Maintable mt
on rt.rid = mt.rid
inner join foodtable ft
on ft.fid = mt.fid


I know its not fid, but collection of ids, but this is to keep your existing code as it is.
Hope it helped. Also you have problem with the dropdown or the gridview, lil bit confusing me now.
0
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22821601
Ya colon should not come , i jus added for the example . .

but still i found that error again ..,,

The query which i wrote is,
string cmd = "SELECT rt.resname, CAST(rt.rid as varchar) + ';' + cast(mt.price as varchar) + ';' + cast(mt.fid as varchar) as resname FROM Resttable rt inner join Maintable mt on rt.rid = mt.rid inner join foodtable ft on ft.fid = mt.fid";



<div><asp:GridView ID="GridView1" runat="server" style="left: 157px; position: relative; top: 141px" OnRowDataBound="GridView1_RowDataBound" AutoGenerateColumns="False">

            <Columns>

(for ur reference)Field 1--><asp:BoundField DataField="fid" HeaderText="fid" />

 field 2--><asp:TemplateField HeaderText="resname">

           <ItemTemplate><asp:DropDownList ID="DropDownList1" runat="server" Style="position: relative" AutoPostBack="true" DataTextField="resname">

                        </asp:DropDownList>

                    </ItemTemplate>

                </asp:TemplateField>

 field 3-->><asp:BoundField HeaderText="price" DataField="price"/>

            </Columns>

        </asp:GridView>

        <asp:Label ID="Label1" runat="server" Style="left: 43px; position: relative; top: 169px"

            Text="Label"></asp:Label>

        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Style="position: relative"

            Text="Button" />

    </div>

In this code , am storing the resname to the dropdown list , 

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

        }

Open in new window

0
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22821706
if my explanation is still confusing , i ll make it clear again ..
i need , to load only restaurant name in the dropdown list . and the resname should be one aftr the othr accordin to fid .  .
and price to be in other field which should change accordingly by the selectedindex of resname which is dropdown....

here i found out one query , which works fine in server ..
but not in local database

select  distinct fid,(select  distinct fname from foodtable where fid=maintable.fid)as fname,stuff( (select ','+resname from resttable where rid = maintable.rid for xml path('')),1,1,'') as resname  from maintable

The followin error and improper way of displaying occured ....
1.Incorrect syntax near 'xml'.(local database)
2.It shows resname 1,resname 2, resname3, resname4 in a single line itself...
but i need to display one by one in the drop down list......
3.I dint no , how to display price accordingly to resname .

if u help me to sort out of the issue ,by giving suggestion , any of ur query r this query ..it ll be gr8....

Thanks a lot for ur work so far....

0
 
LVL 12

Expert Comment

by:techExtreme
ID: 22823111
You say you only want restaurant name in the dropdown and    resname should be one after the other accordin to fid??   restaurant name is not same as resname? Its confusing.

Does your dropdown depend on something else?
DOes your gridview depend on dropdown?
0
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22823409
ho sorry for  confusing...restaurant name and resname both r same only ..i  made a terrible mistake ..
 resname(table is resttable) is my field name of dropdown list which ll be retrieved by  rid of foreign key in maintable. .
resname which depends on fid of maintable .
i mean if a fid  as three diff resname records then dropdown list  should displays resname1,resname2,resname .
Consider the below , which ll be displayed on the web page .
for eg , if pizza of fid 1 is stored in three diff resname such as xyz,abc,def
if burger of fid 2 is stored in two resname such as xyz,abc
 1st row should hold  
Fid       resname (consider this is a dropdown list)      price
1          xyz                                                                                 15         //20 if abc is selected  
           abc   ( if abc is selected it displays its price )      
//similarly for all
           def  
2          xyz                                                                                    20
           abc

Please note :  price should be displayed according to selection of  dropdown list .
 
 
Thanks a lot
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 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

16 Experts available now in Live!

Get 1:1 Help Now