May 22, 2010 at 6:14 pm
Hello everyone,
I have a project from school I am working and I have to create tables in code in SQL server 2008 but havn a prob. I am learning everything online and it is diffucult. Can't figure out how to convert the money CHAR so it recognizes it. When I ex...ecute it, it says I have a syntax error. Any advice would be great. Thank you. Im getting error: 235 state 0 line 1 Level 16: Cannot convert a char value to money. The char value has incorrect syntax. Here is my code:
Create Database [KUDLER]
Create Table [EMPLOYEETABLE]
(
[EmployeeID] [int] IDENTITY (1,1) NOT NULL,
[LastName] [VARCHAR] (25) NOT NULL,
[FirstName] [VARCHAR] (25) NOT NULL,
[Address] [VARCHAR] (max) NULL,
[City] [VARCHAR] (max) NOT NULL,
[State] [CHAR] (2) NOT NULL,
[AreaCode] [CHAR] (3) NOT NULL,
[TelephoneNumber] [CHAR] (7) NOT NULL,
[EmployerInfoReport] [VARCHAR] (max) NOT NULL,
[HireDate] [DATE] NOT NULL,
[Salary] [MONEY] NOT NULL,
[Gender] [CHAR] (1) NOT NULL,
[Age] [CHAR] (2) NOT NULL,
[JobTitle] [VARCHAR] (max) NOT NULL
)
SET IDENTITY_INSERT [EMPLOYEETABLE] ON
Go
INSERT INTO [EMPLOYEETABLE] ( [EmployeeID], [FirstName], [LastName], [Address], [City], [State], [AreaCode], [TelephoneNumber], [EmployerInfoReport], [HireDate], [Salary], [Gender], [Age], [JobTitle])
VALUES('0001', 'Glenn', 'Edelman', '175 Bishop Lane', 'La Jolla', 'Ca', '619', '5550199', 'None', '10/07/03', '11.25/hr', 'M', '25', 'Cashier'),
('0002', 'Eric', 'McMullen', '763 Church st.', 'Lemon Grove', 'Ca', '619', '5550135', 'None', '12/01/02', '7.00/hr', 'M', '23', 'Bagger'),
('0003', 'Raj', 'Slentz', '123 Torrey dr.', 'North Clairmont', 'Ca', '619', '5550123', 'None', '05/15/00', '50,000.00/yr', 'M', '30', 'Asst.Store Manager'),
('0004', 'Erin', 'Broun', '2045 Parkway apt 2B', 'Encinitas', 'Ca', '760', '5550100', 'None', '03/01/03', '7.25/hr', 'F', '25', 'Bagger'),
('0005', 'Donald', 'Carpenter', '927 Second st.', 'Encinitas', 'Ca', '619', '5550154', 'None', '10/15/2003', '7.75/hr', 'M', '25', 'Stocker'),
('0006', 'David', 'Esquivez', '10983 N.Coast Hwy apt 902', 'Encinitas', 'Ca', '760', '5550108', 'None', '9.75/hr', '07/01/03', 'M', '27', 'Asst. Butcher/Seafood'),
('0007', 'Nancy', 'Sharp', '10793 Monticino rd', 'Romona', 'Ca', '858', '5550135', 'None', '06/01/03', '10.50/hr', 'F', '35', 'Cashier')
Go
May 22, 2010 at 6:40 pm
1- You have to code one insert statement for each row you want to add to the target table.
2- You cannot insert '11.25/hr' into a currency data type - currency is basically a number datatype.
I wouldn't define any column as varchar(max), in this particular case max size for each one of them is easily inferable.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 23, 2010 at 12:10 pm
PaulB-TheOneAndOnly (5/22/2010)
1- You have to code one insert statement for each row you want to add to the target table.2- You cannot insert '11.25/hr' into a currency data type - currency is basically a number datatype.
I wouldn't define any column as varchar(max), in this particular case max size for each one of them is easily inferable.
Actually, if this is SQL 2008 (and this is a SQL2008 forum) then the syntax is correct, you do not need multiple insert statements.
You are correct that the problem is trying to insert values like '11.25/hr' (which is a char type value) into a MONEY column...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 23, 2010 at 1:16 pm
Hi,
As already identified by the two previous posters
1) You need to have an insert statement preceeding each row you wish to insert into the table.
2) Having varchar columns specified as max is unneccesary as you should be able to specify an acceptable length.
3) SQL Server is unable to convert the char data to the money which is a numeric type. e.g. the three characters of each example have either "/yr" or "/hr" which SQL Server is interpreting as being a string whihc it can't implicitly convert.
In addition I would suggest the following.
A) The salary column seems to be used to hold different types of information. e.g for most entries it appears to be holding an hourly rate, but for one example it holds a yearly salary. I would suggest introducing a new column called SalaryType and use this to record if the Salary is an hourly rate or a yearly salary. This would allow you to retain the rate type information.
B) I would also suggest changing the age column to hold the actual date of birth of the individual, the actual age could then simply be calculated. If you only hold the actual age it would be incorrect once the persons next birthday passes.
Hope this helps.
Angus
May 23, 2010 at 5:45 pm
Thank all of you for your help!! 🙂 No more errors! )
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply