[MS ACCESS] MDB 쿼리 작성시 유의점

 MDB 쿼리 작성하는데 MSSQL 이랑 왜이리 다른점이...ㅠㅠ

일단... MDB에서는 Update 시 서브쿼리 안먹는다!!

 

예를 들면

Update Emp1 set EmpCode = (Select EmpCode from Emp2 EmpName = @EmpName), EmpNo = @EmpNo where DateTime < @DateTime

위와 같은 구문 안먹음

 

그래서 나는 해결한 방법이 쪼인 썼음

예를 들면 아래와 같이

Update Emp1 e1 Emp2 e2, set EmpCode = (Select EmpCode from Emp EmpName = @EmpName), EmpNo = @EmpNo where DateTime < @DateTime and e1.EmpCode = e2.EmpCode and e2.EmpName = @EmpName 

 

 

아... 또 헛짓 하다 반나절이 거진 다감ㅋㅋㅋㅋ 이번 문제는.... 매개변수 형식이 맞지 않는다 캄.

정확한 메시지는 "매개 변수의 데이터 형식이 잘못되었습니다."

 

아래와 같은 쿼리였다.

 

PARAMETERS [@DeviceName] Text ( 255 ), [@PreCameraName] Text ( 255 ), [@CameraName] Text ( 255 ), [@Enable] Text ( 255 ), [@PurposeName] Text ( 255 ), [@ShapeName] Text ( 255 ), [@PTZ] Bit, [@eMapPosX] Short, [@eMapPosY] Short, [@GisPos] LongBinary, [@TextPos] Text ( 255 ), [@ScheduleName] Text ( 255 ), [@WatchTypeName] Text ( 255 ), [@NormalFramerate] Byte, [@SpeedUp] Bit, [@EventFramerate] Byte, [@KeyFrameOnlyRec] Bit, [@RecordingFPS] Byte, [@PrerecordingTime] Byte, [@PostrecordingTime] Byte, [@OrgPresetNo] Short, [@ShortcutNum] Short, [@Memo] Text ( 255 );

 

UPDATE Camera AS c, CameraPurpose AS cp, CameraShape AS cs, Schedule AS s, WatchType AS wt SET c.Enable = [@Enable], c.CameraName = [@CameraName], c.PurposeCode = cp.PurposeCode, c.ShapeCode = cs.ShapeCode, c.HasPanTilt = [@PTZ], c.eMapPosX = [@eMapPosX], c.eMapPosY = [@eMapPosY], c.TextPos = [@TextPos], c.ScheduleCode = s.ScheduleCode, c.WatchType = wt.WatchTypeCode, c.NormalFramerate = [@NormalFramerate], c.SpeedUp = [@SpeedUp], c.EventFramerate = [@EventFramerate], c.KeyFrameOnlyRec = [@KeyFrameOnlyRec], c.RecordingFPS = [@RecordingFPS], c.PrerecordingTime = [@PrerecordingTime], c.PostrecordingTime = [@PostrecordingTime], c.OrgPresetNo = [@OrgPresetNo], c.ShortcutNum = [@ShortcutNum], c.[Memo] = [@Memo]
WHERE (c.[DeviceCode] = (select d.DeviceCode from Device d where d.DeviceName = @DeviceName) AND c.[CameraName]=@PreCameraName AND cp.PurposeName = @PurposeName AND cs.ShapeName = @ShapeName AND ScheduleName = @ScheduleName AND WatchTypeName = @WatchTypeName );

 

 

문제는 MDB에서는 파라미터 선언했을 경우 대괄호   [, ] 를  해줘야 함. 검색해도 대박 안나와.. 사람을 이렇게 낚나..

 

위 문제가 되던 것 해결 버전

 

PARAMETERS [@DeviceName] Text ( 255 ), [@PreCameraName] Text ( 255 ), [@CameraName] Text ( 255 ), [@Enable] Text ( 255 ), [@PurposeName] Text ( 255 ), [@ShapeName] Text ( 255 ), [@PTZ] Bit, [@eMapPosX] Short, [@eMapPosY] Short, [@GisPos] LongBinary, [@TextPos] Text ( 255 ), [@ScheduleName] Text ( 255 ), [@WatchTypeName] Text ( 255 ), [@NormalFramerate] Byte, [@SpeedUp] Bit, [@EventFramerate] Byte, [@KeyFrameOnlyRec] Bit, [@RecordingFPS] Byte, [@PrerecordingTime] Byte, [@PostrecordingTime] Byte, [@OrgPresetNo] Short, [@ShortcutNum] Short, [@Memo] Text ( 255 );

UPDATE Camera AS c, CameraPurpose AS cp, CameraShape AS cs, Schedule AS s, WatchType AS wt SET c.Enable = [@Enable], c.CameraName = [@CameraName], c.PurposeCode = cp.PurposeCode, c.ShapeCode = cs.ShapeCode, c.HasPanTilt = [@PTZ], c.eMapPosX = [@eMapPosX], c.eMapPosY = [@eMapPosY], c.TextPos = [@TextPos], c.ScheduleCode = s.ScheduleCode, c.WatchType = wt.WatchTypeCode, c.NormalFramerate = [@NormalFramerate], c.SpeedUp = [@SpeedUp], c.EventFramerate = [@EventFramerate], c.KeyFrameOnlyRec = [@KeyFrameOnlyRec], c.RecordingFPS = [@RecordingFPS], c.PrerecordingTime = [@PrerecordingTime], c.PostrecordingTime = [@PostrecordingTime], c.OrgPresetNo = [@OrgPresetNo], c.ShortcutNum = [@ShortcutNum], c.[Memo] = [@Memo]
WHERE  (c.[DeviceCode] = (select d.DeviceCode from Device  d where d.DeviceName = [@DeviceName]) AND c.[CameraName]=[@PreCameraName] AND cp.PurposeName = [@PurposeName]  AND cs.ShapeName = [@ShapeName] AND ScheduleName = [@ScheduleName] AND   WatchTypeName = [@WatchTypeName]  );

 

 

 

위 구문중에

PARAMETERS [@DeviceName] Text ( 255 ), [@PreCameraName] Text ( 255 ), [@CameraName] Text ( 255 ), [@Enable] Text ( 255 ), [@PurposeName] Text ( 255 ), [@ShapeName] Text ( 255 ), [@PTZ] Bit, [@eMapPosX] Short, [@eMapPosY] Short, [@GisPos] LongBinary, [@TextPos] Text ( 255 ), [@ScheduleName] Text ( 255 ), [@WatchTypeName] Text ( 255 ), [@NormalFramerate] Byte, [@SpeedUp] Bit, [@EventFramerate] Byte, [@KeyFrameOnlyRec] Bit, [@RecordingFPS] Byte, [@PrerecordingTime] Byte, [@PostrecordingTime] Byte, [@OrgPresetNo] Short, [@ShortcutNum] Short, [@Memo] Text ( 255 );

 

요 부분 삭제 하면 돌아간다. 하지만 내가 선언한 이유는... 파라미터의 순서때매 매개변수가 잘못 들어 갈 수 있기때문에 순서 지정을 목적으로 선언했다. 파라미터를 선언할땐 대괄호 사용, 쓸땐 대괄호 안사용 했드니 이런 메시지 돌출시킴

MSSQL은 상관없는데 .... 거참..

 

 

이상~