June 18, 2004 at 6:10 am
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
June 18, 2004 at 9:00 am
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
June 18, 2004 at 10:00 am
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
June 21, 2004 at 8:02 am
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
June 21, 2004 at 8:42 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply