problem modifying sqldatasource for dropdownlist control

i use asp.net 2.0 and c#

initially the dropdownlist control is bound to the queryA ("select * from tableA".) for sqldatasource1

now in a button click event, i want to change the query to queryB ("select * from tableA where id = @.id".)

in the code below, i can change the SELECT query of sqldatasource1 from queryA to queryB, but how can i give value to the parameter? please help

protected

void Button1_Click(object sender,EventArgs e)

{

sqldatasource1.SelectCommand = queryB

}

You almost had it, you just needed to add a parameter to the SelectParameters collection like so:

ASPX

<asp:dropdownlist id="ddlProducts" runat="server" datasourceid="sdsProducts" datatextfield="ProductName"datavaluefield="ProductID" /><asp:sqldatasource id="sdsProducts" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString%>"selectcommand="SELECT Products.* FROM Products" /><br /><br /><asp:button id="btnSubmit" runat="server" onclick="btnSubmit_Click" text="Load DropDownList with Seafood products only" />

CODE-BEHIND

protected void btnSubmit_Click(object sender, EventArgs e){sdsProducts.SelectCommand ="SELECT * FROM Products WHERE CategoryID = @.CategoryID";sdsProducts.SelectParameters.Add("CategoryID","8");}

|||hi ecbruk, there is an error saying parameter already exist, please chose a different name ;(|||Are you already setting forth your parameter declaratively within you SqlDataSource?

Problem linking tables

I have two tables, TableA and TableB.
Each has a date in smalldatetime format.
TableA has a qty field in int format.
TableB also has a nvarchar field in which the date is in the format
YYYYMMDD

A simple Query on TableA sum Qty returns a result of 100.
However, When I do a left outer join from TableA to TableB on Date, the
sum of Qty on TableA becomes massivley larger.

What could be causing this?

Regards,
Ciarn(chudson007@.hotmail.com) writes:
> I have two tables, TableA and TableB.
> Each has a date in smalldatetime format.
> TableA has a qty field in int format.
> TableB also has a nvarchar field in which the date is in the format
> YYYYMMDD
> A simple Query on TableA sum Qty returns a result of 100.
> However, When I do a left outer join from TableA to TableB on Date, the
> sum of Qty on TableA becomes massivley larger.
> What could be causing this?

Apparently there is more than one row in TableB with the same date
as in TableA.

This should be evident if you replace SUM(qty) with *, so that
you see all rows in the query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Please learn that rows are not records and columns are
not fields.

>> TableB also has a nvarchar field in which the date is in the format
YYYYMMDD <<

Why? It is redundant. It is in the wrong data type. Do you actively
seek to make queries run longer and be more error prone? What
constriants do you have on that column to prevent illegal dates?

>> When I do a left outer join from TableA to TableB on Date, the sum
of Qty on TableA becomes massivel larger. <<

DATE is a reserved word in SQL and too vague to be proper data element
name. It will be because the same value appears multiple times in
TableB and you get a CROSS JOIN effect.