DateTime column wont update

Question

DateTime column wont update

smknox

Hall of Fame

Points: 3275
More actions
June 18, 2004 at 6:10 am

#111803

Greetings!
I use the following SP to update a table. This is called via ADO code. The ApStart column does not update. All the others execute fine. This is a time and typically just a time value is inserted by the user. Any clue why I can't update this time column?
CREATE PROCEDURE dbo.proc_ApptOVDetailUpdate
(@EmpUpdated varchar(10)
, @ApptID int
, @ApComment varchar(200)
, @ApDate datetime
, @ApSlot int
, @ApStart datetime
, @ApDoc int
, @PtId int
, @ApCodeId int)
AS
Update a
    set a.ApptComment= @ApComment
    , a.ApptDate= @ApDate
    , a.SlotID= @ApSlot
    , a.ApptStart = @ApStart
    , a.DoctorID= @ApDoc
    , a.ApptCodeID= @ApCodeId
FROM Appointment a
WHERE a.AppointmentID= @ApptId
return
GO

Viewing 5 posts - 1 through 4 (of 4 total)

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

I'm a bit confused...

Why does your UPDATE statement have a FROM? A FROM in and UPDATE means you are retrieving data from that table to put into another table. That's not the case with this update.

It should be:

Update Appointment

set ApptComment= @ApComment

, ApptDate= @ApDate

, SlotID= @ApSlot

, ApptStart = @ApStart

, DoctorID= @ApDoc

, ApptCodeID= @ApCodeId

WHERE aAppointmentID= @ApptId

Now the issue with ApptStart. Is ANYTHING being inputted to that column? Remember, DATETIME datatype is both date AND time. If one isn't supplied, SQL Server will use the default. So entering 12:00:00 will actually be 1900-01-01 12:00:00.

Another consideration, is there a TRIGGER on the table that might be affecting the update?

-SQLBill

smknox Hall of Fame Points: 3275 More actions · Answer 2

SQLBill: thanks for your help. THe FROM clause is there because I don't know what I am doing. I will remove it.

I have figured out the problem with the Update. The data type is smalldatetime which offers all the precision I need. My FE is MS Access 2002 which defaults to 12/31/1899 if no date is supplied, which is just outside the smalldatetime range!

So I think I am fine if I can supply the date in my input parameter. Here is my ADO code:

.Parameters.Append .CreateParameter("@ApStart", adDBTimeStamp, adParamInput)

.Parameters("@ApStart").Value = CDate(Me.txtApptStart)

Can you help me with the syntax so that I add a date to the time supplied by the user?

Thanks,

S Knox

SQLBill SSC Guru Points: 51440 More actions · Answer 3

I don't use ADO so I can't help you with that.

About SQL Server programming, I suggest making the BOL your friend. It's very helpful with syntax.

BOL = Books OnLine = MS SQL Server's HELP

Installed as part of the Client Tools

Found at Start>Programs>Microsoft SQL Server>Books OnLine

-SQLBill

smknox Hall of Fame Points: 3275 More actions · Answer 4

Thanks for your patience. I've got it working finally.

I will plan to get more familiar with BOL. I have used it some.

SMK