ASP.NET Data Access Problem

[thebison]

Hi all,

I hope someone can help with this relatively simple problem.
I am building a timesheet application using ASP.NET C# with Visual
Studio 2003.As it is only a protoype application, my database has been
made in MSDE.

As part of my application, I have a 'Resources' table, which holds
employee information. I have the fields FirstName and LastName which
are entered in my data capture form which works fine. The problem
however, is that on other forms, for example, I wish to be able to
select employees from a drop-down list which shows both their first and

last names. At present my drop-down lists can only showHow should I go
about this? Can I create a view or something to achieve this? Or could
I insert a 'FullName'column into my 'Resources' table, which is set by
default to take the first and last names of each new resource and
concatanate it into one string? I worked on an application previously,
which seemed to do something similar, although I don't know how.

What I did try originally was to show the resource information on the
form as a DataGrid, but was unable to successfully pass the
SelectedItem when my 'submit' button was pressed.

Any thoughts, suggestions would be much appreciated!

Thanks again

Al

[Flinky Wisty Pomm]

If you have an employee class, you can add a FullName get property, and
then databind to that. You can even get clever and have a FormatName
method which returns names as Surname, Firstname; Firstname Surname;
etc.

public class Employee
{
public string FirstName
{
get {}
set {}
}

public string Surname
{
get {}
set {}
}

public string FullName
{
get { return string.Format("{0} {1}", FirstName, Surname); }
}
}

[Islamegy®]

If I undertand your Question right.. this is simply done in your query
"Select ID, Firstname+' '+LastName AS FullName From Employees";

in your code you may use reader or dataset, For me i use datareader.
using(SqlDataReader dr = cmd.ExcuteReader(CommandBehviour.CloseConnection)
{
while(dr.Read())
{
ddl1.Items.Add(new ListItem(dr.GetString(0),dr.GetInt32(1)))
}
dr.Close();
}
"thebison" <al************@btinternet.com> wrote in message
news:11*********************@g10g2000cwb.googlegro ups.com...

Hi all,

I hope someone can help with this relatively simple problem.
I am building a timesheet application using ASP.NET C# with Visual
Studio 2003.As it is only a protoype application, my database has been
made in MSDE.

As part of my application, I have a 'Resources' table, which holds
employee information. I have the fields FirstName and LastName which
are entered in my data capture form which works fine. The problem
however, is that on other forms, for example, I wish to be able to
select employees from a drop-down list which shows both their first and

last names. At present my drop-down lists can only showHow should I go
about this? Can I create a view or something to achieve this? Or could
I insert a 'FullName'column into my 'Resources' table, which is set by
default to take the first and last names of each new resource and
concatanate it into one string? I worked on an application previously,
which seemed to do something similar, although I don't know how.

What I did try originally was to show the resource information on the
form as a DataGrid, but was unable to successfully pass the
SelectedItem when my 'submit' button was pressed.

Any thoughts, suggestions would be much appreciated!

Thanks again

Al

[thebison]

Hi everyone!

Thanks, that is exactly what I wanted. My drop-down list now shows the
FullName and it looks great!

While I have your attention....perhaps any of you could assist with
another issue I am having on the same form. The basic point of this
form is to assign a Resource (Employee) onto a Task, which has
previously been assigned to a Project. The way I have laid the form out
is that you can select Projects, Tasks, and Employees all from
drop-down lists. The user can then add some dates, and click submit to
send it to the database.

My problem is that I want the Task drop-down list to automatically
repopulate with the correct tasks when the user selects a Project from
the Project drop-down. What I mean by this is that if for example the
user selects "Redistribution Project" from the list, then only tasks
from that Project will be available in the Tasks drop-down.

I believe I will have to do something with my page_load, specifically
the IsPostBack part...but I am not sure exactly. My code is currently:

if(!IsPostBack)
{
this.sqlDataAdapter1.Fill(this.dsProjectName1);
ddProjectName.DataBind();
this.sqlDataAdapter3.Fill(this.dsTaskName1);
ddTaskName.DataBind();
this.sqlDataAdapter4.Fill(this.dsResourceFullName1 );
ddResName.DataBind();
sqlConnection1.Close();
}

Any help greatly appreciated!

Thanks

Al

[tdavisjr]

Well initially you have to load you Projects in the dropdown list so
that should go in the !IsPostBack. However, the other two are loaded
on demand when a user select a project. So, Your dropdown list for
your project should have autopostback = true. Then,
you must load the TaskName items in the SelectIndexChanged event for
the ddProjectname using the value for the dropdown list of the
projectnames as the filter criteria. The same goes if you want to load
the ResourceName ddl on demand.

[thebison]

Hi again,

Thanks for your help, I have managed to put the relevant ddls in the
right place so that they are posting back at the right time (using the
SelectedIndexChanged part of the code). However I cannot quite get my
RowFilter to work. I'm not sure exactly what I should be putting in
there.

Basically I am populating the Task drop-down list with a DataView. So I
need a RowFilter for the Tasks that filters the TaskID to only show
those that have the same ProjectID as selected from the Project
Drop-Down List.

Something like... dataView3.RowFilter = "Task.ProjectID =
(ddProjects.SelectedValue) ";

The ddProjects is passing the ProjectID as its DataValueField.

Any help appreciated, as ever!

Thanks

Al

[Islamegy®]

The code you wrote for filtering is ok.
I can see that you bind your dropdownlists visually, and generate typed
dataset.. and also the views..
Here is the the code i may use:

DataView dv = dsTaskName1.Tables[0].DefaultView;
ddTaskName.DataSource = dv;
ddTaskName.DataTextField = ..
.......
dataView3.RowFilter = "Task.ProjectID =" + ddProjects.SelectedValue
ddTaskName.Databind();

this will work.. give it a try..

PS... check this usefull link
http://msdn.microsoft.com/practices/...s/default.aspx

"thebison" <al************@btinternet.com> wrote in message
news:11**********************@t31g2000cwb.googlegr oups.com...

Hi again,

Thanks for your help, I have managed to put the relevant ddls in the
right place so that they are posting back at the right time (using the
SelectedIndexChanged part of the code). However I cannot quite get my
RowFilter to work. I'm not sure exactly what I should be putting in
there.

Basically I am populating the Task drop-down list with a DataView. So I
need a RowFilter for the Tasks that filters the TaskID to only show
those that have the same ProjectID as selected from the Project
Drop-Down List.

Something like... dataView3.RowFilter = "Task.ProjectID =
(ddProjects.SelectedValue) ";

The ddProjects is passing the ProjectID as its DataValueField.

Any help appreciated, as ever!

Thanks

Al

[thebison]

Hi,
Thanks, I have this working now! :-)

I now have another question, I've searched all over the web for the
answer, but can't quite work it out. I am filling a list-box with
'Start Date' and 'Finish Date' from a table, and have concatanated
these into a new field, 'Full Date'. However when I DataBind the
list-box it shows the format as '01/10/06 12:00:00 - 07/10/06
12:00:00'. I do not want the times to show. I know how to format one
column, using the DataTextFormatString property, setting it to {0:d} ,
but I can't work out what it will need to be for my 'Full Date' field.

The actual data expression for 'Full Date' is
StartDate + '-' + FinishDate

And I wish it to show in the ListBox as '01/10/06 - 07/10/06'.
Anyone have any ideas on what I should put into DataTextFormatString to
achieve this?

Many Thanks!

Al

[Patrick.O.Ige]

this artcile should help
http://www.codeguru.com/csharp/cshar...cle.php/c4205/

Also i think try formatting the date values before adding it to your listbox
do it in the code behind or try looking at string.Format
Patrick

"thebison" <al************@btinternet.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...

Hi,
Thanks, I have this working now! :-)

I now have another question, I've searched all over the web for the
answer, but can't quite work it out. I am filling a list-box with
'Start Date' and 'Finish Date' from a table, and have concatanated
these into a new field, 'Full Date'. However when I DataBind the
list-box it shows the format as '01/10/06 12:00:00 - 07/10/06
12:00:00'. I do not want the times to show. I know how to format one
column, using the DataTextFormatString property, setting it to {0:d} ,
but I can't work out what it will need to be for my 'Full Date' field.

The actual data expression for 'Full Date' is
StartDate + '-' + FinishDate

And I wish it to show in the ListBox as '01/10/06 - 07/10/06'.
Anyone have any ideas on what I should put into DataTextFormatString to
achieve this?

Many Thanks!

Al

[thebison]

Hi,
Thanks for your reply. I think the reason I am having a problem is that
my 'FullDate' column is being defined as a string, made up of
"StartDate and FinishDate', as shown below:

StartDate + '-' + FinishDate

This means that if it is a string, applying Date Formatting such as
{0:d} (as in the article you suggested) does nothing. When I set the
data type of 'FullDate' to be DateTime, I get the following error

'Cannot convert value '13/03/2006 00:00:00- 19/03/2006 00:00:00' to
Type: System.DateTime.'

So what I am trying to do is cast a string into a DateTime column,
which won't work, obviously. Is there any other way I can accomplish
this?

Many thanks

Al