Drop down List

[billy_111]

Hey,

I have the following list box:-

<asp:DropDownList ID="DropDownList1" runat="server"
            DataSourceID="SqlDataSource1" DataTextField="cat_name"
            DataValueField="cat_name" Width="258px">
        </asp:DropDownList>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="Data Source=tcp:esql2k801.discountasp.net;Initial Catalog=SQL2008_673062_kids;User ID=SQL2008_673062_kids_user;Password=*******;"
            SelectCommand="SELECT [cat_id], [cat_name] FROM [tbl_gallery_cat]">
        </asp:SqlDataSource>

I have a button, and on button click i have the following code:-

protected void Button1_Click(object sender, EventArgs e)

    {

        string connStr = "Data Source=tcp:esql2k801.discountasp.net;Initial Catalog=SQL2008_673062_kids;User ID=SQL2008_673062_kids_user;Password=******;";

        SqlConnection dbConn = new SqlConnection(connStr);



        string sqlStrImage = "SELECT gallery_id, cat_name, image, caption " +

        "FROM tbl_gallery_images WHERE cat_name = @cat_name";



        SqlCommand dbCommandImage = new SqlCommand(sqlStrImage, dbConn);



        dbCommandImage.Parameters.Add("@cat_name", SqlDbType.Char).Value = DropDownList1.SelectedValue;



        dbConn.Open();



        SqlDataReader dbReaderImage = dbCommandImage.ExecuteReader();



        while (dbReaderImage.Read())

        {

            Label1.Text += "<div><a href='http://kidsunlimi2.eweb102.discountasp.net/galleries/" + dbReaderImage["image"].ToString() + "'> " +

            "<img src='http://kidsunlimi2.eweb102.discountasp.net/galleries/" + dbReaderImage["image"].ToString() + "' alt='' border='0'/></a>" +

            "<span>" + dbReaderImage["caption"].ToString() + "</span></div>";

        }



        dbReaderImage.Close();

        dbConn.Close();

    } 

Now, when i click on the button, the code does not show the results of the SELECT as it does not recognize the dropdown boc vale i think..

Take a look at this page to see it in action:-

http://kidsunlimi2.eweb102.discounta...galleries.aspx

How can i write the code to fix his problem?


Regards

[NightStalker-DNS]

Ok, first things first.

You would want to move that connection string to the web.config instead so that you do not declare it over and over again. Imagine if you connection string changes when its like this.

I am not a fan of using a sqlDataSource to bind like this, as its hard to tell what exactly is going on and when its getting bound if you dont no how it works.

Next, the command. Do not use parameters.Add. Use this:
dbCommandImage.Parameters.AddWithValue("@cat_name", DropDownList1.SelectedValue);

Next, you would want to use a repeater instead of of just writing html to a label. And bind the repeater to the datareader.

To figure out y the qry is not working, write out the selected value to a label or something to see what its getting.

Hope this helps

[USPatriot]

I second NightStalker. Start here:

http://articles.sitepoint.com/articl...peater-control

Repeater is great if you want more control over the outputted HTML (without resorting to MVC).

Very good article to get you started.

Add the connectionstring to the web.config like so:

<connectionStrings>
<add name="MyConnectionName" connectionString="Data Source=computername\SQLEXPRESS;Initial Catalog=Databasename;User ID=sa;Password=dev123"
providerName="System.Data.SqlClient" />
</connectionStrings>

Then, access it like so:

string myConnStr = ConfigurationManager.ConnectionStrings["MyConnectionName"].ConnectionString;

Good Luck

[billy_111]

Hey,

Thanks, i actually found what i was doing wrong, i changed the code to this:-

    protected void Button1_Click(object sender, EventArgs e)

    {



        string connStr = "Data Source=tcp:esql2k801.discountasp.net;Initial Catalog=SQL2008_673062_kids;User ID=SQL2008_673062_kids_user;Password=******;";

        SqlConnection dbConn = new SqlConnection(connStr);



        string sqlStrImage = "SELECT gallery_id, cat_name, image, caption " +

        "FROM tbl_gallery_images WHERE cat_name = " + DropDownList1.SelectedValue;



        SqlCommand dbCommandImage = new SqlCommand(sqlStrImage, dbConn);



        dbConn.Open();



        SqlDataReader dbReaderImage = dbCommandImage.ExecuteReader();



        while (dbReaderImage.Read())

        {

            Label1.Text += "<div><a href='http://kidsunlimi2.eweb102.discountasp.net/galleries/" + dbReaderImage["image"].ToString() + "'> " +

            "<img src='http://kidsunlimi2.eweb102.discountasp.net/galleries/" + dbReaderImage["image"].ToString() + "' alt='' border='0'/></a>" +

            "<span>" + dbReaderImage["caption"].ToString() + "</span></div>";

        }



        dbReaderImage.Close();

        dbConn.Close();

    } 

Now a problem i am having is when i select an option from the drop down and hit submit the button, i get the following error:-

Invalid column name 'One'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Invalid column name 'One'.

'One' is the value of the dropdown box that i selected, it does exactly the same when i select another option from the dropdown..

Regards

[USPatriot]

I think it was a string formatting problem. Try this....



string sqlStrImage = "SELECT gallery_id, cat_name, image, caption FROM tbl_gallery_images WHERE cat_name = " + "'" + DropDownList1.SelectedValue + "'";

[NightStalker-DNS]

Yes, USPatriots solution will work, but rather you parameters as it is a lot safer. Doing things like this leaves holes for SQL injection. And i strongly suggest u use a repeater