4 Replies - 4099 Views - Last Post: 13 July 2012 - 09:58 PM Rate Topic: -----

#1 toxifier  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 29
  • Joined: 27-September 11

Connectivity Issue between Database & Drop Down List

Posted 11 July 2012 - 09:04 AM

Hello
I have been learning ASP.Net since sometime. Recently I was trying to make 2 Drop Down List controls. One was to be for the Country List and the other for corresponding the State List. I used the following code:
Front-end ASP Code
<tr>
            <td>
                <asp:Label ID="regcon" runat="server" Text="Country"></asp:Label>
            </td>
            <td>
                <asp:DropDownList ID="Cntrylst" runat="server" AutoPostBack="True">
                </asp:DropDownList>
                <asp:RequiredFieldValidator ID="regvalcnt" runat="server" 
                    ControlToValidate="Cntrylst" Display="Dynamic" ErrorMessage="*" ForeColor="Red"></asp:RequiredFieldValidator>
            </td>
        </tr>
         <tr>
            <td>
                <asp:Label ID="Label1" runat="server" Text="State"></asp:Label>
            </td>
            <td>
                <asp:DropDownList ID="stlst" runat="server" 
                    onload="Cntrylst_SelectedIndexChanged">
                </asp:DropDownList>
                <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" 
                    ControlToValidate="Cntrylst" Display="Dynamic" ErrorMessage="*" ForeColor="Red"></asp:RequiredFieldValidator>
            </td>
        </tr>


back-end C# Code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

public partial class Default4 : System.Web.UI.Page
{
    SqlConnection obj_con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Users.mdf;Integrated Security=True;User Instance=True");
    SqlDataReader obj_dr;
    SqlCommand obj_com;
    protected void Page_Load(object sender, EventArgs e)
    {
        statelist();
        countrylist();
    }
protected void countrylist()
    {
        string cntstr = "select * from CountryList";
        obj_com = new SqlCommand(cntstr, obj_con);
        obj_con.Open();
        obj_dr = obj_com.ExecuteReader();
        Cntrylst.DataTextField = "Countries";
        Cntrylst.DataValueField = "Countries";
        Cntrylst.DataSource = obj_dr;
        Cntrylst.DataBind();
        obj_con.Close();
    }

    protected void Cntrylst_SelectedIndexChanged(object sender, EventArgs e)
    {
        string statestr = "select  '" + Cntrylst.SelectedValue.ToString() + "' from StateList";
        // string statestr = "select Afghanistan from StateList";  //if i use this instead it works!
        obj_com = new SqlCommand(statestr, obj_con);
        obj_con.Open();
        
        obj_dr = obj_com.ExecuteReader();
        
        stlst.DataTextField = Cntrylst.SelectedValue.ToString();
        //stlst.DataTextField = "Afghanistan";  //if i use this instead it works!
        stlst.DataValueField = Cntrylst.SelectedValue.ToString();
        //stlst.DataValueField = "Afghanistan";  //if i use this instead it works!
        stlst.DataSource = obj_dr;
        stlst.DataBind();
        obj_con.Close();
        
    }
}


I have made 2 tables inside my database named Users.mdf. One named "CountryList" and other named "StateList". In CountryList there is just one Column named "Countries" and in the StateList I have made one column for each Country, like one column named Afghanistan for it's states; one for USA for it's states; and so on. I don't wish to change the structure of my tables. With the current tables that I have made, can anyone help me solve the problem? When I execute the above code, an exception is thrown :
"DataBinding: 'System.Data.Common.DataRecordInternal' does not contain a property with the name 'Afghanistan'." I know I haven't handeled the events properly and they are being thrown at wrong time, but the problem is this exception if you can help me deal with this without having me to change my tables!

This post has been edited by toxifier: 11 July 2012 - 09:10 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Connectivity Issue between Database & Drop Down List

#2 zeeshanef  Icon User is offline

  • D.I.C Head

Reputation: 17
  • View blog
  • Posts: 71
  • Joined: 14-April 12

Re: Connectivity Issue between Database & Drop Down List

Posted 11 July 2012 - 10:16 AM

Did you check the value of [statestr] variable, before it is executed, and do you see the exact query which you expect to run!
Was This Post Helpful? 0
  • +
  • -

#3 toxifier  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 29
  • Joined: 27-September 11

Re: Connectivity Issue between Database & Drop Down List

Posted 12 July 2012 - 04:24 AM

View Postzeeshanef, on 11 July 2012 - 10:16 AM, said:

Did you check the value of [statestr] variable, before it is executed, and do you see the exact query which you expect to run!

yes! it has the expected value!
Was This Post Helpful? 0
  • +
  • -

#4 zeeshanef  Icon User is offline

  • D.I.C Head

Reputation: 17
  • View blog
  • Posts: 71
  • Joined: 14-April 12

Re: Connectivity Issue between Database & Drop Down List

Posted 12 July 2012 - 09:43 AM

you said:
"select Afghanistan from StateList";  //if i use this instead it works!


If above works then, there is just one difference which is single quotations across Field Name remove these symbols from "dynamic" query and check again:

string statestr = "select " + Cntrylst.SelectedValue.ToString() + " from StateList";

Was This Post Helpful? 1
  • +
  • -

#5 toxifier  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 29
  • Joined: 27-September 11

Re: Connectivity Issue between Database & Drop Down List

Posted 13 July 2012 - 09:58 PM

View Postzeeshanef, on 12 July 2012 - 09:43 AM, said:

you said:
"select Afghanistan from StateList";  //if i use this instead it works!


If above works then, there is just one difference which is single quotations across Field Name remove these symbols from "dynamic" query and check again:

string statestr = "select " + Cntrylst.SelectedValue.ToString() + " from StateList";

Honestly, I was trying to solve this in the morning today and got it right(I hadn't read your reply till then). I just came here to tell that I did this and got it right and then saw that I have got the same reply from you already. It works completely fine now and yes your reply is correct. Thanks a lot for your time! Good Day friend! ^_^
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1