ASP with SQL DB and VB code behind. Editing returned data

Question

I'm trying to code a timesheet app for a project I'm doing, and for the best part of a month I've no progressed with a key part of the system. I need to be able to edit and add data into the app via a weekly view Mon-Sun. I have a SQL (server) pivot that can return the data I eneter daily, but since its using dynamic headers for the dates in the week, the gridview function to edit, will not work. I've tried binding data but this has the same issues, with the dates. I think I need to create a form that has text boxes to enter the data and update this way, but how can I populate such a form using the query I have ? I also need to be able to add new rows. I'm at the end of my tether with this and on the verge of abandoning the coding as its just stressing me out now.

Heres my SQL that displays the data exactly how I want:

ALTER PROCEDURE [dbo].[list_weekly_times]
as



DECLARE @offSetmon int, @fromdt datetime,@todt datetime,@offsetsun int, @COLName    varchar (max)
,     @SQL        varchar (max), @fromdate as date, @todate as date, @offsetmonval as int, @offsetsunval as int

SELECT @offSetmon = 1
select @fromdt = GETDATE()
SELECT @offSetSun = 7
select @todt = GETDATE()
SELECT @fromdt = CONVERT(DATETIME, CONVERT(INT, @fromdt) - (DATEPART(WEEKDAY, @fromdt) - @offSetmon))
SELECT @todt = CONVERT(DATETIME, CONVERT(INT, @todt) - (DATEPART(WEEKDAY, @todt) - @offSetsun))


select @COLName    = ''
 ,    @SQL        = ''


while @FromDt <= @ToDt
begin
      if    (@COLName = '')
      begin
            set   @COLName    = '[' + convert (varchar (10), @FromDt, 121) + ']'
      end
      else
      begin
            set   @COLName    = @COLName + ', [' + convert (varchar (10), @FromDt, 121) + ']'
      end
      set   @FromDt = dateadd (d, 1, @FromDt)
end
print @COLName
select      @SQL  = 'select project_code, activity_code,' + @COLName + 
              ' From (select project_code, activity_code, project_date, Project_time from timesheet) as P 
                pivot 
                  (     sum (Project_time)
                        for project_date in (' + @COLName + ')
                  ) as pvt
                order by project_code

              '
print @SQL
exec  (@SQL)

Just won't work with editing as pivots are designed for data display rather than data edit.

Maybe the pivot approach is wrong and another direction is needed.

Any help, tutorials, examples would help.

Is this for a classic asp or asp.net? Because grid view and binding data sounds like you are talking about asp.net yet you tagged this as classic asp. — 03Usr
– 03Usr, Commented Jul 9, 2012 at 17:20
Couldn't you set the columns to the days of the week (Mon - Sunday) rather than the dates, so that they're always the same? You could display the date range separately from the grid-view columns (perhaps above the grid-view). — gmm
– gmm, Commented Mar 6, 2013 at 7:45

DougM · Accepted Answer · 2014-01-26 22:57:13Z

1

STOP. You are colluding the client-side display of your data with the physical structure of your data, by way of a dynamically written SQL statement. This gets you the worst of all possible worlds.

Ideally, you would use or create a control in ASP.NET that can take your nice and narrow rows straight from the timesheet table, and handle the wrapping of data-entry entirely within the ASP.NET layer.

If for some reason you cannot do this and need to have the by-week data on-server (such as, say, you have a weekly payroll that needs to be approved in its entirety), you should create an updateable table or view dedicated to timecard_rows, with seven fields for each day of the week and some "base date" indicator so you can coerce all seven into and out of the timesheet table.

answered Jan 26, 2014 at 22:57

DougM

2,88819 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Patrick Hofman · Accepted Answer · 2014-01-27 11:03:45Z

Why not use triggers for this.

Say you have a view like this:

create view TimeCards
as
select yourDate FirstDayOfWeek
,      sum(case when day = 1 then hours else 0) end sun
,      sum(case when day = 2 then hours else 0) end mon
,      sum(case when day = 3 then hours else 0) end tue
,      sum(case when day = 4 then hours else 0) end wed
,      sum(case when day = 5 then hours else 0) end thu
,      sum(case when day = 6 then hours else 0) end fri
,      sum(case when day = 7 then hours else 0) end sat
from   table
group
by     WeekNumber

Then update the hours like this in a trigger:

create trigger TimeCards_Upd on TimeCards
instead of update
as
begin
    delete table
    where  date = yourDate + 0 -- sun

    insert
    into  table
    ( yourDate
    , hours
    )
    values
    ( date + 0 -- sun
    , sun -- hours from sunday
    )

    -- repeat for every other day
end;

Collectives™ on Stack Overflow

ASP with SQL DB and VB code behind. Editing returned data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related