Numeric Data type

M

Melody

Hello, I really need some help. I have an Access 2003 form and a SQL 2000
table. I have a stored procedure to insert records. I keep getting a
"Precision is invalid" when I try to execute the stored procedure because I
changed some of my fields to "Numeric" from "Integer" because I need decimal
places. Here is my info:

In Access module:
Public Function InsertTimeSheet(pDate, pStartTime, pLunchOut, pLunchIn,
pEndTime, pReasonOT, pVacationHours, pSickHours, pOtherHours, pUnpaidHours,
pEmployeeID, pHolidayHours)
Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = CurrentProject.Connection
.CommandText = "spTimeSheetInsert"
.CommandType = adCmdStoredProc
.CommandTimeout = 6000

.Parameters.Append .CreateParameter("@StartDate", adDBDate,
adParamInput, 8, pDate)
.Parameters.Append .CreateParameter("@StartTime", adDBDate,
adParamInput, 8, pStartTime)
.Parameters.Append .CreateParameter("@LunchOut", adDBDate, adParamInput,
8, pLunchOut)
.Parameters.Append .CreateParameter("@LunchIn", adDBDate, adParamInput,
8, pLunchIn)
.Parameters.Append .CreateParameter("@EndTime", adDBDate, adParamInput,
8, pEndTime)
.Parameters.Append .CreateParameter("@ReasonOT", adVarChar,
adParamInput, 20, pReasonOT)
.Parameters.Append .CreateParameter("@VacationHours", adNumeric,
adParamInput, 9, pVacationHours)
.Parameters.Append .CreateParameter("@SickHours", adNumeric,
adParamInput, 9, pSickHours)
.Parameters.Append .CreateParameter("@OtherHours", adNumeric,
adParamInput, 9, pOtherHours)
.Parameters.Append .CreateParameter("@UnpaidHours", adNumeric,
adParamInput, 9, pUnpaidHours)
.Parameters.Append .CreateParameter("@EmployeeID", adInteger,
adParamInput, 0, pEmployeeID)
.Parameters.Append .CreateParameter("@HolidayHours", adInteger,
adParamInput, 0, pHolidayHours)

Set rst = .Execute
End With
Set cmd = Nothing
End Function

My stored procedure:
CREATE PROCEDURE [dbo].[spTimeSheetInsert]
@StartDate datetime,
@StartTime datetime,
@LunchOut datetime,
@LunchIn datetime,
@EndTime datetime,
@ReasonOT varchar(20),
@VacationHours numeric,
@SickHours numeric,
@OtherHours numeric,
@UnpaidHours numeric,
@EmployeeID int,
@HolidayHours int
AS
SET NOCOUNT ON
INSERT INTO [dbo].[tblTimeSheet] (
[StartDate],
[StartTime],
[LunchOut],
[LunchIn],
[EndTime],
[ReasonOT],
[VacationHours],
[SickHours],
[OtherHours],
[UnpaidHours],
[EmployeeID],
[HolidayHours]
) VALUES (
@StartDate,
@StartTime ,
@LunchOut,
@LunchIn,
@EndTime,
@ReasonOT,
@VacationHours,
@SickHours,
@OtherHours,
@UnpaidHours,
@EmployeeID,
@HolidayHours
)
GO

My table, the Vacation, Sick, Other and Unpaid Hours are all

Data Type: Numeric
Length: 9
Allow Nulls: True

Precision: 19
Scale: 4

I have changed these fields to decimal, double etc and keep getting the same
error. I am so confused now I really just don't know what to use for my
field type. The data I am storing in these fields numeric and may have up to
2 decimal places. For example:

3.5
7
4.75

Any help would be very greatly appreciated.
 
M

Mary Chipman [MSFT]

you don't need the numeric data type if you only need two decimal
places of precision - use smallmoney instead. I didn't pore through
your code, but if you don't specify the exact same precision and scale
each time for decimal/numeric data, SQL Server considers each to be a
separate data type.

-mary

Hello, I really need some help. I have an Access 2003 form and a SQL 2000
table. I have a stored procedure to insert records. I keep getting a
"Precision is invalid" when I try to execute the stored procedure because I
changed some of my fields to "Numeric" from "Integer" because I need decimal
places. Here is my info:

In Access module:
Public Function InsertTimeSheet(pDate, pStartTime, pLunchOut, pLunchIn,
pEndTime, pReasonOT, pVacationHours, pSickHours, pOtherHours, pUnpaidHours,
pEmployeeID, pHolidayHours)
Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = CurrentProject.Connection
.CommandText = "spTimeSheetInsert"
.CommandType = adCmdStoredProc
.CommandTimeout = 6000

.Parameters.Append .CreateParameter("@StartDate", adDBDate,
adParamInput, 8, pDate)
.Parameters.Append .CreateParameter("@StartTime", adDBDate,
adParamInput, 8, pStartTime)
.Parameters.Append .CreateParameter("@LunchOut", adDBDate, adParamInput,
8, pLunchOut)
.Parameters.Append .CreateParameter("@LunchIn", adDBDate, adParamInput,
8, pLunchIn)
.Parameters.Append .CreateParameter("@EndTime", adDBDate, adParamInput,
8, pEndTime)
.Parameters.Append .CreateParameter("@ReasonOT", adVarChar,
adParamInput, 20, pReasonOT)
.Parameters.Append .CreateParameter("@VacationHours", adNumeric,
adParamInput, 9, pVacationHours)
.Parameters.Append .CreateParameter("@SickHours", adNumeric,
adParamInput, 9, pSickHours)
.Parameters.Append .CreateParameter("@OtherHours", adNumeric,
adParamInput, 9, pOtherHours)
.Parameters.Append .CreateParameter("@UnpaidHours", adNumeric,
adParamInput, 9, pUnpaidHours)
.Parameters.Append .CreateParameter("@EmployeeID", adInteger,
adParamInput, 0, pEmployeeID)
.Parameters.Append .CreateParameter("@HolidayHours", adInteger,
adParamInput, 0, pHolidayHours)

Set rst = .Execute
End With
Set cmd = Nothing
End Function

My stored procedure:
CREATE PROCEDURE [dbo].[spTimeSheetInsert]
@StartDate datetime,
@StartTime datetime,
@LunchOut datetime,
@LunchIn datetime,
@EndTime datetime,
@ReasonOT varchar(20),
@VacationHours numeric,
@SickHours numeric,
@OtherHours numeric,
@UnpaidHours numeric,
@EmployeeID int,
@HolidayHours int
AS
SET NOCOUNT ON
INSERT INTO [dbo].[tblTimeSheet] (
[StartDate],
[StartTime],
[LunchOut],
[LunchIn],
[EndTime],
[ReasonOT],
[VacationHours],
[SickHours],
[OtherHours],
[UnpaidHours],
[EmployeeID],
[HolidayHours]
) VALUES (
@StartDate,
@StartTime ,
@LunchOut,
@LunchIn,
@EndTime,
@ReasonOT,
@VacationHours,
@SickHours,
@OtherHours,
@UnpaidHours,
@EmployeeID,
@HolidayHours
)
GO

My table, the Vacation, Sick, Other and Unpaid Hours are all

Data Type: Numeric
Length: 9
Allow Nulls: True

Precision: 19
Scale: 4

I have changed these fields to decimal, double etc and keep getting the same
error. I am so confused now I really just don't know what to use for my
field type. The data I am storing in these fields numeric and may have up to
2 decimal places. For example:

3.5
7
4.75

Any help would be very greatly appreciated.
Click to expand...
 
M

Melody

Hi MAry, Thank you so much for your response. I changed the data types in my
table to smallmoney and I changed the datatype in my stored proecdure to
smallmoney as well. But what do I put for the datatype when I send the
parameter? There is no smallmoney type. I left it as decimal and changed
the length to 4 but I am still getting that error. Should I change it to
something else? Thank you.

Mary Chipman said:
you don't need the numeric data type if you only need two decimal
places of precision - use smallmoney instead. I didn't pore through
your code, but if you don't specify the exact same precision and scale
each time for decimal/numeric data, SQL Server considers each to be a
separate data type.

-mary

Hello, I really need some help. I have an Access 2003 form and a SQL 2000
table. I have a stored procedure to insert records. I keep getting a
"Precision is invalid" when I try to execute the stored procedure because I
changed some of my fields to "Numeric" from "Integer" because I need decimal
places. Here is my info:

In Access module:
Public Function InsertTimeSheet(pDate, pStartTime, pLunchOut, pLunchIn,
pEndTime, pReasonOT, pVacationHours, pSickHours, pOtherHours, pUnpaidHours,
pEmployeeID, pHolidayHours)
Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = CurrentProject.Connection
.CommandText = "spTimeSheetInsert"
.CommandType = adCmdStoredProc
.CommandTimeout = 6000

.Parameters.Append .CreateParameter("@StartDate", adDBDate,
adParamInput, 8, pDate)
.Parameters.Append .CreateParameter("@StartTime", adDBDate,
adParamInput, 8, pStartTime)
.Parameters.Append .CreateParameter("@LunchOut", adDBDate, adParamInput,
8, pLunchOut)
.Parameters.Append .CreateParameter("@LunchIn", adDBDate, adParamInput,
8, pLunchIn)
.Parameters.Append .CreateParameter("@EndTime", adDBDate, adParamInput,
8, pEndTime)
.Parameters.Append .CreateParameter("@ReasonOT", adVarChar,
adParamInput, 20, pReasonOT)
.Parameters.Append .CreateParameter("@VacationHours", adNumeric,
adParamInput, 9, pVacationHours)
.Parameters.Append .CreateParameter("@SickHours", adNumeric,
adParamInput, 9, pSickHours)
.Parameters.Append .CreateParameter("@OtherHours", adNumeric,
adParamInput, 9, pOtherHours)
.Parameters.Append .CreateParameter("@UnpaidHours", adNumeric,
adParamInput, 9, pUnpaidHours)
.Parameters.Append .CreateParameter("@EmployeeID", adInteger,
adParamInput, 0, pEmployeeID)
.Parameters.Append .CreateParameter("@HolidayHours", adInteger,
adParamInput, 0, pHolidayHours)

Set rst = .Execute
End With
Set cmd = Nothing
End Function

My stored procedure:
CREATE PROCEDURE [dbo].[spTimeSheetInsert]
@StartDate datetime,
@StartTime datetime,
@LunchOut datetime,
@LunchIn datetime,
@EndTime datetime,
@ReasonOT varchar(20),
@VacationHours numeric,
@SickHours numeric,
@OtherHours numeric,
@UnpaidHours numeric,
@EmployeeID int,
@HolidayHours int
AS
SET NOCOUNT ON
INSERT INTO [dbo].[tblTimeSheet] (
[StartDate],
[StartTime],
[LunchOut],
[LunchIn],
[EndTime],
[ReasonOT],
[VacationHours],
[SickHours],
[OtherHours],
[UnpaidHours],
[EmployeeID],
[HolidayHours]
) VALUES (
@StartDate,
@StartTime ,
@LunchOut,
@LunchIn,
@EndTime,
@ReasonOT,
@VacationHours,
@SickHours,
@OtherHours,
@UnpaidHours,
@EmployeeID,
@HolidayHours
)
GO

My table, the Vacation, Sick, Other and Unpaid Hours are all

Data Type: Numeric
Length: 9
Allow Nulls: True

Precision: 19
Scale: 4

I have changed these fields to decimal, double etc and keep getting the same
error. I am so confused now I really just don't know what to use for my
field type. The data I am storing in these fields numeric and may have up to
2 decimal places. For example:

3.5
7
4.75

Any help would be very greatly appreciated.
Click to expand...
Click to expand...
 
M

Mary Chipman [MSFT]

Currency should work.

-mary

Hi MAry, Thank you so much for your response. I changed the data types in my
table to smallmoney and I changed the datatype in my stored proecdure to
smallmoney as well. But what do I put for the datatype when I send the
parameter? There is no smallmoney type. I left it as decimal and changed
the length to 4 but I am still getting that error. Should I change it to
something else? Thank you.

Mary Chipman said:
you don't need the numeric data type if you only need two decimal
places of precision - use smallmoney instead. I didn't pore through
your code, but if you don't specify the exact same precision and scale
each time for decimal/numeric data, SQL Server considers each to be a
separate data type.

-mary

Hello, I really need some help. I have an Access 2003 form and a SQL 2000
table. I have a stored procedure to insert records. I keep getting a
"Precision is invalid" when I try to execute the stored procedure because I
changed some of my fields to "Numeric" from "Integer" because I need decimal
places. Here is my info:

In Access module:
Public Function InsertTimeSheet(pDate, pStartTime, pLunchOut, pLunchIn,
pEndTime, pReasonOT, pVacationHours, pSickHours, pOtherHours, pUnpaidHours,
pEmployeeID, pHolidayHours)
Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = CurrentProject.Connection
.CommandText = "spTimeSheetInsert"
.CommandType = adCmdStoredProc
.CommandTimeout = 6000

.Parameters.Append .CreateParameter("@StartDate", adDBDate,
adParamInput, 8, pDate)
.Parameters.Append .CreateParameter("@StartTime", adDBDate,
adParamInput, 8, pStartTime)
.Parameters.Append .CreateParameter("@LunchOut", adDBDate, adParamInput,
8, pLunchOut)
.Parameters.Append .CreateParameter("@LunchIn", adDBDate, adParamInput,
8, pLunchIn)
.Parameters.Append .CreateParameter("@EndTime", adDBDate, adParamInput,
8, pEndTime)
.Parameters.Append .CreateParameter("@ReasonOT", adVarChar,
adParamInput, 20, pReasonOT)
.Parameters.Append .CreateParameter("@VacationHours", adNumeric,
adParamInput, 9, pVacationHours)
.Parameters.Append .CreateParameter("@SickHours", adNumeric,
adParamInput, 9, pSickHours)
.Parameters.Append .CreateParameter("@OtherHours", adNumeric,
adParamInput, 9, pOtherHours)
.Parameters.Append .CreateParameter("@UnpaidHours", adNumeric,
adParamInput, 9, pUnpaidHours)
.Parameters.Append .CreateParameter("@EmployeeID", adInteger,
adParamInput, 0, pEmployeeID)
.Parameters.Append .CreateParameter("@HolidayHours", adInteger,
adParamInput, 0, pHolidayHours)

Set rst = .Execute
End With
Set cmd = Nothing
End Function

My stored procedure:
CREATE PROCEDURE [dbo].[spTimeSheetInsert]
@StartDate datetime,
@StartTime datetime,
@LunchOut datetime,
@LunchIn datetime,
@EndTime datetime,
@ReasonOT varchar(20),
@VacationHours numeric,
@SickHours numeric,
@OtherHours numeric,
@UnpaidHours numeric,
@EmployeeID int,
@HolidayHours int
AS
SET NOCOUNT ON
INSERT INTO [dbo].[tblTimeSheet] (
[StartDate],
[StartTime],
[LunchOut],
[LunchIn],
[EndTime],
[ReasonOT],
[VacationHours],
[SickHours],
[OtherHours],
[UnpaidHours],
[EmployeeID],
[HolidayHours]
) VALUES (
@StartDate,
@StartTime ,
@LunchOut,
@LunchIn,
@EndTime,
@ReasonOT,
@VacationHours,
@SickHours,
@OtherHours,
@UnpaidHours,
@EmployeeID,
@HolidayHours
)
GO

My table, the Vacation, Sick, Other and Unpaid Hours are all

Data Type: Numeric
Length: 9
Allow Nulls: True

Precision: 19
Scale: 4

I have changed these fields to decimal, double etc and keep getting the same
error. I am so confused now I really just don't know what to use for my
field type. The data I am storing in these fields numeric and may have up to
2 decimal places. For example:

3.5
7
4.75

Any help would be very greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Top