April 28, 2004 at 5:34 pm
I am trying to update the Appointment table as follows:
UPDATE Appointment
@ApptID Datetime
SET CheckInTime=#" & Format(Now(), "m/d/yyyy h:nn") & "#
,RoomNum=Null
,RoomTime=Null
,CheckoutTime=Null
,PatientCheckIn=1
,PatientCheckOut=0
WHERE AppointmentID= @ApptID
I get a syntax error on the 2nd line. Also, the checkin time needs to be the system time which I know is not correct above. I pass the ApptID value in from ADO code from an Access app.
Thanks so much!!
Smk
April 28, 2004 at 7:11 pm
Hi Smk,
--You need to firstly declare your variable
-- Also, assuming that @ApptId is an id field and not a datetime field I changed the datatype to int
DECLARE @ApptID int
-- Then you need to set it to a particular value (in this case I used 2)
SET @ApptID = 2
UPDATE Appointment
-- Getdate() will use the system datetime
SET CheckInTime= getdate()
, RoomNum = NULL
, RoomTime = NULL
, CheckoutTime = NULL
, PatientCheckIn = 1
, PatientCheckOut = 0
WHERE AppointmentID = @ApptID
Is this what you want?
Cheers,
Angela
April 29, 2004 at 5:05 am
Thanks, Angela.
You are right, ApptID is an integer (I think my brain was only firing half time)I am passing the value of ApptID in from ADO code. So would it be:
UPDATE Appointment
DECLARE @ApptID int
SET CheckInTime=getdate()
etc.
WHERE AppointmentID = @ApptID
Here is the ADO code:
Dim cmdNew As ADODB.Command
Dim cnn As ADODB.Connection
Dim strCnn As String
strCnn = "Provider=sqloledb;Data Source=xxx;" & _
"Initial catalog=xxx;User Id=xxx;Password=xxx;"
Set cnn = New ADODB.Connection
cnn.Open strCnn
Set cmdNew = New ADODB.Command
With cmdNew
Set .ActiveConnection = cnn
.CommandText = "procApptCheckInTimeInsert"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@ApptID", adInteger, adParamInput)
.Parameters("@ApptID").Value = cmbBDate.ItemData(cmbBDate.ListIndex)
.Execute
End With
cnn.Close
Set cnn = Nothing
Thanks!
SMK
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply