A couple of, I hope Basic Questions RE SqlDataSources and scripting

Greetings!

I am writing a proof of concept ASP.net application for my employer and I have run into a couple perplexing issues. First off, In my Page_Load event I have the following code.

txOTHrs.Attributes[

"onBlur"] ="return calculateTotal(this)";

The onblur event handler is coded as follows

function calculateTotal(tb)

{

var regHrs = document.getElementById ("txRegHrs").value; var otHrs = document.getElementById("txOtHrs").value; var rate = document.getElementById("txRate").value; var regAmt = rate * regHrs; var otAmt= rate * ( otHrs * 1.5 ); var total = otAmt + regAmt;

document.getElementById(

"txTotal").value = total;

}

Everything seems to work fine. The onBlur event is handled and, as I expect the value in the txTotal text box is updated correctly. The problem occurs when I try to access that value in the codebehind page. When ever I look at txTotal.Text in the debugger it is always blank. This has left me scratching my head as I can clearly see a value on the web page. Have I missed some step where I have to notify the server that a value in the control has changed ?

My second question is, I hope a very easy one to answer. In WinForms when I have a dataset I can access the individual rows ( in an untyped dataset ) by speficying dataset.tables["tableName"].Rows[index]. Is there any similar mechanism for accessing the individual rows, and fields in the SqlDataSource object in ASP.net ?

Any help anyone can provide is greatly appreciated!!

If you are comming from a "Windows Forms" world, then there are a few key concepts that you are going to need to understand... most especially is the "Page Lifecycle" which I will mention further down... but to answer your questions quickly:

sabrecat:

Have I missed some step where I have to notify the server that a value in the control has changed ?

- Yes... you need to "PostBack" to the server at any time that you want to reflect changes.

sabrecat:

In WinForms when I have a dataset I can access the individual rows ( in an untyped dataset ) by speficying dataset.tables["tableName"].Rows[index]. Is there any similar mechanism for accessing the individual rows, and fields in the SqlDataSource object in ASP.net ?

- Again, Yes... here is a small example:

DataView myDataView = (DataView)this.MarketDataSource.Select(DataSourceSelectArguments.Empty);

foreach (DataRow rowin myDataView.Table.Rows)
{
// do something here :)
}

// Or you can do...

string firstPersonsLastName = (string)myDataView.Table.Rows[0]["LastName"];

Here is the "Page Lifecycle" that I mentioned earlier:
http://msdn2.microsoft.com/en-us/library/ms178472.aspx
http://www.eggheadcafe.com/articles/o_aspNet_Page_LifeCycle.jpg

Read the MSDN article, save that picture to your desktop and look at it often :)

I hope this helps.

Peace,

|||

Thank you very much for the reply! You have been tremendously helpful!

I thought that the data that I was putting into the control wasnt getting back to the serve. I wasnt sure what mechanism to use however to get it into the server. To actually perform thepostback I would use the __doPostBack function that ASP.Net puts into the page right ? I mean in my script I'd do somthing like

function calculateTotal(e, tb)
{
var regHrs = document.getElementById ("txRegHrs").value;
var otHrs = document.getElementById("txOtHrs").value;
var rate = document.getElementById("txRate").value;
var regAmt = rate * regHrs;
var otAmt= rate * ( otHrs * 1.5 );
var total = otAmt + regAmt;
document.getElementById("txTotal").value = total;
__doPostBack(tb,e);
}

Since my calulateTotal function right now only has the one argument (tb) I assume then that I would have to change my codebehind page to look somthing like

txOTHrs.Attributes[

"onBlur"] ="return calculateTotal(event, this)";

instead of

txOTHrs.Attributes["onBlur"] ="return calculateTotal(this)";

Regarding the SqlDataSource. Does the argument DataSourceSelectArguments.Empty make the invocation of the Select method NOT go out to the database again ? If this is not the case and the database is hit again then I would want to get the data into a DataView at the time that I do my initial select on the database and then put that object into the session to make it persistant between page reloads ?

Again thank you very much for all the insights and the links to some really good data.

A basic sql query problem

Suppose we get two following tables:

Table A:
[USER_ID] [varchar] (11) NOT NULL ,
[COURSE_ID] [varchar] (11) NOT NULL ,

Table B:

[COURSE_ID] [varchar] (11) NOT NULL ,
[COURSE_NAME] [varchar] (50) NOT NULL ,
[COURSE_NO] [varchar] (15) NULL ,
[BEGIN_DATE] [datetime] NULL ,
[END_DATE] [datetime] NULL ,
[CREATER] [varchar] (11) NOT NULL ,

and during the execution of my program, I can get the current use's id (USER_ID), sayU0001.

How can I retrieve the result set containing[COURSE_NAME],[COURSE_ID],but the current user's id (U0001) have Not been assigned in Table A.

Thanks in advance.

Ricky.

if u know what the course will be taken by the userid U0001 then just query from table B and insert the data of user in the table A.say u want to assign the user U0001 to "Eng01" query with select course_id,course_name from tableB where course_name ='Eng01' and then assign this course_id and user_id to tableA...it is not clear to me that what u wants to do ?|||

Are you talking about joining between two table?

If yes, try SELECT TableA.User_ID, TableB.CourseID, TableB.Course_Name FROM TableB LEFT OUTER JOIN TableA ON TableA.CourseID = TableB.CourseID

I haven't test it yet, but try searching for "LEFT OUTER JOIN" if it doesn't work.

Hope that help.

|||

Sorry for my unclear description of my question first.

And nr2ea, you provide me helpful hint.

I have one more question: is there any difference between LEFT JOIN and LEFT OUTER JOIN?

Ricky.

A basic question?

Hello all!,
This is probably pretty simple. I think I'm trying to hard on this, and
can't see the logic. If I have a table(products) with a productID, prodname.
And a another table called user, with UserID and name. And I add a UserID
column to my Product table. How can link the userID to the product ID. So i
f
user1 sells, apples and grapes, apple and grapes will have UserID1 matched t
o
it, just like userid2 sells Oranges, and so on. Hopethis makes sense!
Thanks!!
RudyHi Ruby
This looks like a database design issue. To reduce the redundancy,
to can create a new Table UserProducts and add UserID and ProdID to it
Ex:
User ID | Name
1 | User-1
2 | User-2
3 | User-3
ProdID | Name
1 | Apples
2 | Grapes
3 | Oranges
UserID | ProdID
1 | 1
1 | 2
3 | 3
Hope this answers the question
thanks and regards
Chandra
"Rudy" wrote:

> Hello all!,
> This is probably pretty simple. I think I'm trying to hard on this, and
> can't see the logic. If I have a table(products) with a productID, prodnam
e.
> And a another table called user, with UserID and name. And I add a UserID
> column to my Product table. How can link the userID to the product ID. So
if
> user1 sells, apples and grapes, apple and grapes will have UserID1 matched
to
> it, just like userid2 sells Oranges, and so on. Hopethis makes sense!
> Thanks!!
> Rudy|||Thank you! That does make sence. I knew I was making it more difficult tha
n
it had to be.
Thank You!!
Rudy
"Chandra" wrote:
> Hi Ruby
> This looks like a database design issue. To reduce the redundancy,
> to can create a new Table UserProducts and add UserID and ProdID to it
> Ex:
> User ID | Name
> 1 | User-1
> 2 | User-2
> 3 | User-3
> ProdID | Name
> 1 | Apples
> 2 | Grapes
> 3 | Oranges
> UserID | ProdID
> 1 | 1
> 1 | 2
> 3 | 3
> Hope this answers the question
> thanks and regards
> Chandra
>
>
> "Rudy" wrote:
>

A basic question: Removing duplicate results from Max function

Hi,

Say I have a table Job with columns name, date, salary . I want to get
the name ,date and salary for the date when that person earned maximum
salary. I am using something like

SELECT X.name,X.date,X.salary
FROM job X
WHERE X.salary IN
(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);

The problem is ; if a person earns maximum salary on two dates, both of
the dates are printed. I just want to get any one of those two rows.
I tried

SELECT X.name,Min(X.date),X.salary
FROM job X
WHERE X.salary IN
(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);

but it gives error.
Can anybody please suggest a solution?

Regards,
Aamir(aamircheema@.gmail.com) writes:
> Say I have a table Job with columns name, date, salary . I want to get
> the name ,date and salary for the date when that person earned maximum
> salary. I am using something like
>
> SELECT X.name,X.date,X.salary
> FROM job X
> WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
>
> The problem is ; if a person earns maximum salary on two dates, both of
> the dates are printed. I just want to get any one of those two rows.
> I tried
> SELECT X.name,Min(X.date),X.salary
> FROM job X
> WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
> but it gives error.

SELECT a.name, a.date, a.salary
FROM tbl a
JOIN (SELECT b.name, date = MAX(b.date)
FROM tbl b
JOIN (SELECT name, salary = MAX(salary)
FROM tbl
GROUP BY name) c ON c.name = b.name
AND c.salary = b.salary
GROUP BY a1.name) b ON a.name = b.name
AND a.date = b.date

This presumes that (name, date) is unique, and a person does not have
two salaries the same day.

The inner selects are derived tables - sort of virtual temp tables within
the query. A very powerful tool to write complex queries. A derived table
is independent of the outer query, and this why the alias b can be reused.
Note that they are not necessarily computed in whole - the optimizer often
recast computation order for a very very effceient query plan.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||aamircheema@.gmail.com wrote:
> Hi,
> Say I have a table Job with columns name, date, salary . I want to get
> the name ,date and salary for the date when that person earned maximum
> salary. I am using something like
>
> SELECT X.name,X.date,X.salary
> FROM job X
> WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
>
> The problem is ; if a person earns maximum salary on two dates, both of
> the dates are printed. I just want to get any one of those two rows.
> I tried
> SELECT X.name,Min(X.date),X.salary
> FROM job X
> WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
> but it gives error.
> Can anybody please suggest a solution?
> Regards,
> Aamir

In SQL Server 2005:

WITH j AS
(SELECT name, date, salary,
RANK() OVER (PARTITION BY name ORDER BY salary DESC, date DESC) rnk
FROM job)
SELECT name, date, salary
FROM j
WHERE rnk = 1 ;

That assumes the combination of (name,salary,date) is unique. If it
isn't then just add other columns to the ORDER BY specification to make
a key.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||On 10 Jun 2006 07:48:38 -0700, aamircheema@.gmail.com wrote:

(snip)
>I tried
>SELECT X.name,Min(X.date),X.salary
>FROM job X
>WHERE X.salary IN
> (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
>but it gives error.
>Can anybody please suggest a solution?

Hi Aamir,

Here's a third suggestion:

SELECT X.name,Min(X.date),X.salary
FROM job X
WHERE X.salary IN
(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name)
GROUP BY X.name, X.salary;

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP|||
Thanks Everybody. That helped a lot

A basic design question

When I set the relationship between two tables with a one-to-many relationship and I want all records deleted from the many side when a row is deleted from the one side, how should I set the Insert and Update Specs (Delete and Update) CASCADE or NO ACTION?

Then if there is a lookup table on the many side how should it be set?

I generally set up the PK-FK constraints and write my own DELETE statements rather than rely on CASCADE Delete. That way I always know the flow of how the data will/should be deleted.|||I was thinking that too. But I was also thinking about how SQL Server will overwrite my thinking if the cascade is not set properly.|||

Hi Jack,

I would suggest to set Update to CASCADE, because the change to the lookup table will be cascaded to the child table.

It does not make difference whether Insert is set to CASCADE or NO ACTION.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!