ASP/Database problem (Long)

Bakedgoods

Bakedgoods

Bakedgoods

Capodecina
Permabanned
Joined
31 Dec 2003
Posts
5,172
Location
Barrow-In-Furness
To get started here's the two tables i'm talking about. It's a timesheet application.

tblCodes
CodeID
CodeName
CodeDescription
CodeCategory
CodeOwner
CodeStatus

tblHours
ID
TimeCode
Date
Hours
Username
Comments

The problem is this, the CodeStatus field can be changed and that's mainly to stop people booking hours to projects that have completed or are on hold etc. It does however have a couple of different Status values that will allow someone to book hours to that code.

What I would like to do is when I start creating the reports, is to be able to report on the different status values for each code.

For example, the code may begin life with a value of "Initiation" and hours get booked to that, but it is then changes to 'In Progress'. I would like to be able to report on them seperately, at the moment it in not reflected in the hours table.

I'm thinking that if I just add another field to the hours table (TimeCodeStatus) and then somehow get the current status of the code when the user is inputting information, it should be fine?

The ASP part

If all the above sounds fine, how can I get this information into a TextBox within a DetailsView in input mode?

It's a little past what I know how to do so i'm looking for some advice, thanks for any help guys it is appreciated.

:)
 
Last edited:
couldnt you design it a bit differently so that you have multiple entries for the same project... i.e. the project in 'initiation' stage is a different entry to the 'in progress' entry.

You will have to redesign your tables slightly to normalize them. Unfortunately my brain isn't working today so you will have to work that out yourself :-p
 
I'd rather not because it means I then need to worry about linking the codes together in a parent/child fashion for reporting purposes.
 
Anyone else got any ideas?

I know there is much, much better developers than me on here.
 
Last edited:
What if I just create a query which will get the CodeStatus value based on the CodeID that has been selected for input into the tblHours, for example:

SELECT CodeStatus

FROM lward.tblCodes

WHERE CodeID = (@CodeID)



When inputting the data CodeID is selected using a drop down menu, but how could I get the value of CodeStatus into a text box depending on the CodeID that is chosen?
 
Have you created a three tiered application? (Data Access, Business Logic, Presentation)

You can use the Business Logic Layer to call the function related to your query in the Data Access Layer to get the CodeStatus.
 
I may have skipped the Business Logic Layer part ;)

Is there no way to do it without out it?

I don't really know how to call an SQL query through just pure VB code, surely I could just do some code behind which runs the above query then sets a TextBox with the value? Not that I know how to actually do that :\

Hmmmm
 
Last edited:
You really need to get your head around the Business Logic Layer, it will make it a whole lot easier. ;)

Anyway, an alternative would be to create your query in one of your Datasets and then use code to access the query in your dataset to get the value you want.

Take a look at the Data Access tutorial, about 1/3 way down the page it has an example of accessing the dataset.

http://www.asp.net/learn/data-access/tutorial-01-vb.aspx

Once you have that you need to access the row to get the value of the column you want.

There is lot's of information on Google for DataSets, DataTables, DataAdapters, accessing rows etc, so just pick out what you need.

If you get this to work, then you should be able to create a BLL as it is pretty much the same idea. :)
 
I'll get looking cheers.

I just don't fully understand the point of the BLL, i'm done everything I need without one and with relative ease (it may have been hard at first but simple once I understand it).

Just to clarify what i'm doing...

I have a DetailsView which allows the user to input the following at the moment:

Time code (Drop down)
Date
Time spent (Drop down)
Username (Done automatically)


I basically just need to have another field in this DetailsView called TimeCodeStatus that automatically gets the Status of a code from tblCodes.

Ideally I guess this could occur outside the DetailsView and hidden from the user but i'm not too sure how to do that.
 
What I would suggest is putting the code on the time code drop down.
Add the query in your dataset to get the status.
In your drop down event link to your dataset, get the value from the query.
Access the row event to get the value of the status in the query.
Assign the value you retrieved to the text property of the textbox.

The linking of the dataset and accessing the row is shown in the code on the tutorial page I posted.

Is your code all in a seperate .vb page?
 
Yeah all of the code is in a seperate file.

So from what you're saying above, what would actually happen is...

- Someone selects a time code from the drop down menu
- This then calls a routine OnSelectedItem or whatever it's called
- This links to the query which will get the status

I think I can do all of it besides the 4th step, cheers for trying to help dude :]
 
Last edited:
Yep, just a few other things to point out.

I think you may also need to import your dataset, if you look at the top of the code file it should already have some imports. What you want is to import your datasets table adapter so that the dataset can be read to create the datatable.
Look at your dataset ids/names you should find one with tableadapter on the end, simply import the id/name.

As for the last bit, when you run a page the parent controls are read.
Because the textbox is embedded it is a child control in the DetailsView and the DetailsView is a parent, so it isn't read. This is also the same for other controls like drop downs, labels, buttons etc.

To get around this you use the findcontrol property of the detailsview to search for the control, but you need to create a new control in your code that links to the actual textbox to get it's properties. So you would do something like the following:

Code: 
Dim txt1 As New TextBox = CType(DetailsView1.FindControl("TextBox1"), TextBox)

You would also use something similar to above if you need to access the properties of any other child controls embedded in a panel or any data grids/views.
 
Thanks, i'll give it a go when I get chance.

I've been spending the morning learning how to add/delete roles etc after deploying the website.
 
Ok here we go, I may have gone about this the wrong way I don't know, but here's the code and i'll highlight the errors.

Code: 
Protected Sub TimeCodeDropDown_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)

        Dim CodeID As New Integer
        Dim TimeCodeDropDown As New DropDownList
        Dim TimeCodeStatusTextBox As New TextBox

        Dim codestatusAdapter As New ManHoursTableAdapters.CodeStatusTableAdapter()
        Dim codeStatus As ManHours.CodeStatusDataTable

        TimeCodeDropDown = CType(DetailsView1.FindControl("TimeCodeDropDown"), DropDownList
        TimeCodeStatusTextBox = CType(DetailsView1.FindControl("TimeCodeStatus"), TextBox


        CodeID = TimeCodeDropDown.SelectedValue

        codeStatus = [B][COLOR="Red"]codestatusAdapter.GetCodeStatus()[/COLOR][/B]

        TimeCodeStatusTextBox.Text = codeStatus.ToString()



    End Sub

For the highlighted line I get the error:

Argument not specified for parameter 'CodeID' of 'Public Overrideable Function GetCodeStatus (CodeID as Integer).

My SQL Query is:

Code: 
SELECT CodeStatus FROM lward.tblCodes

WHERE CodeID = (@CodeID)

I've only got as far as the code behind and i'm not sure if what i've done is right or anything so I do need some opinions as well please folks.

Thanks for any help :)
 
Well from the error you are getting and the query you have a parameter called CodeID.

In your code when you are getting the data to populate the datatable, you haven't included a parameter.
So you need to pass the CodeID when trying to get your query.
e.g.

Code: 
codeStatus = codestatusAdapter.GetCodeStatus(CodeID)
 
Thanks that's took the error away but unfortunately it's not actually working. The page loads fine but when I select a value from the drop down menu it just adds "CodeStatus" to the the text box. Which ever value I pick, once it posts back "CodeStatus" just appears in the text box.
Here's the behind page code:

Code: 
Protected Sub TimeCodeDropDown_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)

        Dim CodeID As New Integer
        Dim TimeCodeDropDown As New DropDownList
        Dim TimeCodeStatusTextBox As New TextBox

        Dim codestatusAdapter As New ManHoursTableAdapters.CodeStatusTableAdapter()
        Dim codeStatus As ManHours.CodeStatusDataTable

        TimeCodeDropDown = CType(DetailsView1.FindControl("TimeCodeDropDown"), DropDownList)
        TimeCodeStatusTextBox = CType(DetailsView1.FindControl("TimeCodeStatus"), TextBox)

                CodeID = TimeCodeDropDown.SelectedValue

        codeStatus = codestatusAdapter.GetCodeStatus(CodeID)

        TimeCodeStatusTextBox.Text = codeStatus.ToString()


    End Sub

Here's the code I added to the web page:

Code: 
<asp:DropDownList ID="TimeCodeDropDown" runat="server" DataSourceID="ObjectDataSource1"DataTextField="CodeID" DataValueField="CodeID" SelectedValue='<%# Bind("TimeCode", "{0}") %>'Width="155px" AppendDataBoundItems="True" OnSelectedIndexChanged="TimeCodeDropDown_SelectedIndexChanged" AutoPostBack="True" >
 
What are you displaying in your drop down list at the moment?

I think because you have set the SelectedValue property on the drop down it is cancelling out the DataValueField property which is your CodeID.
 
You must log in or register to reply here.
Back
Top Bottom