Data conversion failed. [ OLE DB status value (if known) = 0 ]

  • I was trying to do an insert command to populate my table but I receive the error in the subject line.

    This is my code:

    INSERT INTO Employees

    (E_Id,LastName,FirstName,Address,PhoneNumber,HireDate,Salary,Age,JobTitle)

    VALUES('1','Edelman','Glenn','175 Bishops Lane La Jolla California','619-555-0199','10/7/2003','$21,500.00','64','Cashier');

    This is my output:

    Major Error 0x80004005, Minor Error 26306

    > INSERT INTO Employees

    (E_Id,LastName,FirstName,Address,PhoneNumber,HireDate,Salary,Age,JobTitle)

    VALUES('1','Edelman','Glenn','175 Bishops Lane La Jolla California','619-555-0199','10/7/2003','$21,500.00','64','Cashier')

    Data conversion failed. [ OLE DB status value (if known) = 0 ]

  • Could you please list out all the columns used in the insert with the SQL data types?

    Also is the e_id column an identity column? I would bet the problem is with e_id being a numeric type (i.e. int,smallint) and / or indentity column.

  • The following is the columns and data types:

    Column Name Data Type Length Allow Nulls Unique Primary Key

    E_Id int 4 No No Yes

    LastName nvarchar 100 No No Yes

    FirstName nvarchar 100 Yes No No

    Address nvarchar 200 Yes No No

    PhoneNumber numeric 9 Yes No No

    HireDate nvarchar 15 Yes No No

    Salary numeric 13 yes No No

    age numeric 5 Yes No No

    Job Title nvarchar 200 Yes No No

  • Okay from your Data Type List I see several issues:

    1. You have the value '1' going into an int column. The value should not have the single quotes around it.

    2. The same with the Phone number. You should change the data type in the table to varchar not numeric if you plan to store the dash characters and have single quotes. If you want to leave it numeric then the data has to be all digits and be large enough to support 10 digits.

    3. Your HireDate is nvarchar and technically there would be no problem with the '10/7/2003' going into this column but best practice would dictate you change this type to DateTime or Date(SQL 2008).

    4. Your salary is a numeric type and you cannot put '$21,500.00' in this type of field. If you change the datatype to Money

    then that value will succeed. If you leave the type as is, then you will need to change the value going into 21500 with no decimals as you have not defined decimals in the numeric type.

    If you fix those issues your INSERT should work unless I have missed some other error.

    INSERT INTO Employees

    (E_Id,LastName,FirstName,Address,PhoneNumber,HireDate,Salary,Age,JobTitle)

    VALUES('1','Edelman','Glenn','175 Bishops Lane La Jolla California','619-555-0199','10/7/2003','$21,500.00','64','Cashier')

    Group: General Forum Members

    The following is the columns and data types:

    Column Name Data Type Length Allow Nulls Unique Primary Key

    E_Id int 4 No No Yes

    LastName nvarchar 100 No No Yes

    FirstName nvarchar 100 Yes No No

    Address nvarchar 200 Yes No No

    PhoneNumber numeric 9 Yes No No

    HireDate nvarchar 15 Yes No No

    Salary numeric 13 yes No No

    age numeric 5 Yes No No

    Job Title nvarchar 200 Yes No No

  • Thank You,

    I will try it.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply