What is the datatype?

Question

What is the datatype?

Longshanks

SSC Enthusiast

Points: 115
More actions
May 3, 2007 at 3:11 pm

#173245

A little background: In a Windows .NET application I have a datagrid that refuses to allow editing of one of the date columns. A job is given a start date, but the due date can be set later. Users don't want a blank due date, however, so a derived due date is displayed (i.e., it isn't real, the job's due date isn't scheduled until a due date is actually entered), so when the record is saved in the application this default due date is committed.

[SAMPLE DATA]
create table zTemp_temp (
      job varchar(10) not null,
      startdate datetime not null,
      duedate datetime null
      )
go
insert zTemp_temp
      select '18766', '4/12/2007', '4/28/2007' union all
      select '18896', '4/21/2007', NULL union all
      select '18249', '4/28/2007', '5/1/2007' union all
      select '18527', '5/1/2007', null
go
CREATE PROCEDURE zwp_TestDate
as
select job, startdate, isnull(duedate, dateadd(day, 7, startdate)) as duedate
from zTemp_temp

If you applied the above to a datagrid you'd find the startdate column is editable, but not duedate, and I don't know why.  A datagrid accept input that is valid for that datatype. Since it rejects dates (as well as string and numeric input) I have to wonder what this last column's datatype is coming out of SQL. I can't imagine it isn't datetime. If it isn't datetime that would explain the problem, and I could work out a solution from there.
So, what is the datatype of duedate?
Thanks in advance for any help.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mike Nuessler Right there with Babe Points: 788 More actions · Answer 1

I think it is most likely having problems updating because it is thinking that it is a derived column thanks to the alias even though the alias is the same as the field name. That being said, not sure what you need to do to fix it..just wanted to put the idea out there.

In the beginning the Universe was created. This has made a lot of people very angry and has been widely regarded as a bad move. Douglas Adams (1952-2001)

Longshanks SSC Enthusiast Points: 115 More actions · Answer 2

That was my thinking. I have also tried "cast(isnull(duedate, dateadd(day, 7, startdate)) AS DATETIME) as enddate", but no joy there either.

I'm not sure I can get .NET to display a date for each NULL.

Regards,

-longshanks

Sergiy SSC Guru Points: 110192 More actions · Answer 3

You datagrid contains calculated data not data from column.

That's why you cannot update it.

You may wish to create a view with select statement you posted and set INSTEAD OF INSERT, UPDATE, DELETE trigger on it which will work out data in grid back to the data in table according to the rules you must know.

Then it may work.

But I'm not really sure. VB6 grids fail to work with views.

_____________
Code for TallyGenerator

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 4

Longshanks,

We had a similar issue with a site we just built. Our date (when NULL) was changed to ‘1/1/1900’, then in the PreRender of the GRID caught the “1/1/1900” and replaced with “-“. It does seem that .NET doesn’t like nulls in date fields (from my experience, that is)

I realize that you want to display a default date and have it updatable though. You may want to include another ~~two columns~~ column in your query say something like …

SELECT

job

,startdate

,duedate

,isnull(duedate, dateadd(day, 7, startdate)) as duedateDisplay

--EDIT,isNullDate = CASE WHEN dusdate IS NULL THEN 1 ELSE 0 END

FROM

zTemp_temp

You would show the duedateDisplay field in the grid, but not the duedate field. Then during the OnUpdate event, catch the change in duedateDisplay and apply it to the duedate field.

I’m not sure if this would work or not, but it would be my first attempt.

Let me know if this works

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

David Burrows SSC Guru Points: 65111 More actions · Answer 5

Use the duedate in the query and in the datagrid ItemDataBound event change the output value accordingly.

Far away is close at hand in the images of elsewhere.
Anon.

Longshanks SSC Enthusiast Points: 115 More actions · Answer 6

If I change the SPROC back to simply select the DueDate, then generate the DataSet from that, then change the SPROC to calculate the DueDate without regenerating the DataSet, the column is editable and updates work fine.