DataSet Question (ASP.net)

[Bakedgoods]

When I try to add the following query to my TableAdapter for my Hours table I get the "The new command text returns data with schema different from the schema of the main query. Check your query's command text if this is not desired" prompt.

The query i'm trying to use is:

SELECT     tblHours.ID, tblHours.TimeCode, tblHours.Date, tblHours.Hours, tblHours.Username, tblHours.Comments, tblHours.Approved, 
                      tblCodes.CodeOwner

FROM         tblHours INNER JOIN
                      tblCodes ON tblHours.TimeCode = tblCodes.CodeID

WHERE Approved = '1' AND CodeOwner = @UserName

This then stops other things such as editing or inserting working where i've used other queries from the TableAdapter.

Do I need to create a different TableAdapter for the query?

 

[Bakedgoods]

Look who's dishing out the help again, thanks

I will be updating, so i'm guessing i'll have to manually edit the update command in the new TableAdapter?

I basically need to use the above query to get hours that have not been approved and are assigned to a certain person to be approved, then they will be approving the hours.

The Approved column will be a bit type so I can just use a CheckBox to approve an entry.

 

[Bakedgoods]

I'm sturggling with the update command.

I've created a new dataset, the main query for the dataset is the one posted above. I need to select the information so that a user can view unapproved hours, then approve them (the update part).

So the main query of the new dataset is:

SELECT     tblHours.ID, tblHours.TimeCode, tblHours.Date, tblHours.Hours, tblHours.Username, tblHours.Comments, tblHours.Approved, 
                      tblCodes.CodeOwner

FROM         tblHours INNER JOIN
                      tblCodes ON tblHours.TimeCode = tblCodes.CodeID

WHERE Approved = '0' AND CodeOwner = @UserName

This did not automatically create an update query as expeted but i'm struggling to make one in the query builder, can you give any advice?

The only field that needs updating is the Approved field.

 

[Bakedgoods]

The two tables concerned are:

tblHours
ID - int
TimeCode - int
Date - smalldatetime
Hours - numeric
Username - nvachar
Comments - text
Approved - bit

tblCodes
CodeID - int
CodeName - nvachar
CodeDescription - text
CodeCategory - nvachar
CodeOwner - nvachar
CodeStatus - nvachar

This is a timesheet system.

What i'm trying to do is set up an hours approval system so that hours are approved by the appropriate code owner.

The approved field will be a simple CheckBox that the code owner approves. They will simply open a page which should trigger a gridview with any hours for a Code that they own based on their logged in Username. Does that make sense?

Thanks for your help

I'm thinking this tutorial here is probably my answer:

http://www.asp.net/learn/data-access/tutorial-69-cs.aspx

 

[Bakedgoods]

I'm abit worried about this.

I'm not sure I can do it without totally re-doing my application and using BLLs. I really don't need to use BLLs for anything else either.

Grrr this isn't good :\

 

[Bakedgoods]

Ok so i've created a correlated SQL query so that the INSERT, UPDATE and DELETE commands are auto-generated.

How and where can I add the WHERE part at though? To clarify....

When a Project Manager clicks the approve hours page, all the hours that are unapproved for codes they are the CodeOwner of will be displayed.

The basic query is

SELECT ID, TimeCode, Date, Hours, Username, Comments, Approved, 

(SELECT CodeOwner FROM lward.tblCodes WHERE tblCodes.CodeID = tblHours.TimeCode) as CodeOwner

FROM lward.tblHours

I'm not sure where to add the where CodeOwner = @username part at. For example, I thought it would look like this:

SELECT ID, TimeCode, Date, Hours, Username, Comments, Approved, 

(SELECT CodeOwner FROM lward.tblCodes WHERE tblCodes.CodeID = tblHours.TimeCode) as CodeOwner

FROM lward.tblHours

WHERE Approved = '0' AND CodeOwner = @UserName

I get the error "Invalid column name CodeOwner", obviously this is because CodeOwner part needs to be inside the brackets, I can't seem to get the format right though and it doesn't work.

Thanks for any help

 

[Bakedgoods]

I've got the query looking like this, it works when I used Approved = '1' but when I try use '0' I don't get any results. I also tried using '(0)'.

SELECT ID, TimeCode, Date, Hours, Username, Comments, Approved, 

(SELECT CodeOwner FROM lward.tblCodes WHERE tblCodes.CodeID = tblHours.TimeCode AND CodeOwner = @UserName) as CodeOwner

FROM lward.tblHours

WHERE Approved = '(0)'

I'm using a bit field for approved