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?

 

[KevinCB]

I'm going to guess on what you have done here, correct me if I'm wrong.

You've created a DataSet based on a table in your database (probably the hours table ).
You have then added a query within this adapter where you have then linked to another table, which has prevented the insert and update statements from working with your other queries.

TableAdapters seem to only like inserting and updating where the query is based on one table, as soon as you add a query that links two tables together it doesn't like it.

If you just want to use the query you posted to select from the table, then it should work if you create a new TableAdapter.
It will probably come up with the same message but since you aren't inserting or updating it shouldn't be a problem.

 

[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.

 

[KevinCB]

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

Yep, add a new query and select the 'Update' query rather than 'Select'. Then you can create your own update query, same goes for inserts and deletes as well.

 

[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.

 

[KevinCB]

To update a row in a table your going to need the value of the primary key for that row.
In that case, I would grab the ID field for the row you need when you use the select query to retrieve your data.

Then update just the approved field, maybe something similar to:

UPDATE tblHours
SET tblHours.Approved = 1
WHERE tblHours.ID = @HoursID

 

[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 :\

 

[KevinCB]

BLLs are used to control/manipulate how you want to access/insert/update a database table. For example, say you have created a tableadapter based on a table in your database. You want to insert some data, but not all of it at once. You can create a function in a BLL to insert only the data you want for that table, without having to add a seperate insert query in your DAL.

You don't have to re-do your whole application to add BLLs.

Create your BLL files. Any datasources that you are using for GridViews or dropdown lists, delete them and add an object data source linking to your BLL. If your BLL is set up right visual studio should do the rest e.g. adding the insert and update queries to the data source.

In your situation, there is an alternative way I can see where you don't need a BLL.

Add the Update query I posted to the tableadapter you use for your hours table.
Add a GridView control on a page using only the select query you posted above from the new tableadapter you created.
On the GridView hide your ID field, then 'Enable Selection' this will select the ID field value.
Add a button on the page, then add your hours table tableadapter in the code behind to reference the update query. Use 'GridView1.SelectedValue' as the HoursID parameter.

 

[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