Unable to insert records into IN_UnitOfMeasure  Topic is solved

Support for Microsoft SQL Server in the context of Jiwa installations.

Unable to insert records into IN_UnitOfMeasure

Postby Atronics » Mon Mar 27, 2017 8:26 am

I get the following error when running this insert query. Despite several hours searching for a solution, I am stuck. Can you advise the solution, please.

error = "Msg 8169, Level 16, State 2, Line 31
Conversion failed when converting from a character string to uniqueidentifier."

Query
INSERT IN_UnitOfMeasure (RecID,IN_Main_InventoryID,Name, Inner_IN_UnitOfMeasure_RecID, QuantityInnersPerUnitOfMeasure, IsSell, IsPurchase,PartNo, Barcode, IsEnabled, ItemNo, LastSavedDateTime)
SELECT
CAST(NEWID() AS UNIQUEIDENTIFIER),
IN_Main.InventoryID,
'Bag',
'',
AA_IN_OrderUnits.OrderUnits,
1,
1,
LTRIM((RTRIM(IN_Main.PartNo)) +'B'),
'',
1,
1,
GETDATE()
FROM IN_Main, AA_IN_OrderUnits
WHERE IN_Main.InventoryID = AA_IN_OrderUnits.InventoryID

Table AA_IN_OrderUnits is a custom table. Also tried wwithout the CAST function and received the same error.
Atronics
Frequent Contributor
Frequent Contributor
 
Posts: 119
Joined: Fri Feb 29, 2008 4:40 pm
Topics Solved: 10

Re: Unable to insert records into IN_UnitOfMeasure  Topic is solved

Postby Scott.Pearce » Mon Mar 27, 2017 8:49 am

You are supplying an empty string for the "Inner_IN_UnitOfMeasure_RecID" value. You should provide a NULL. Also, you need not cast the output of NEWID() to UNIQUEIDENTIFIER (It is already a UNIQUEIDENTIFIER).

Code: Select all
INSERT IN_UnitOfMeasure (RecID,IN_Main_InventoryID,Name, Inner_IN_UnitOfMeasure_RecID, QuantityInnersPerUnitOfMeasure, IsSell, IsPurchase,PartNo, Barcode, IsEnabled, ItemNo, LastSavedDateTime)
SELECT
NEWID(),
IN_Main.InventoryID,
'Bag',
NULL,
AA_IN_OrderUnits.OrderUnits,
1,
1,
LTRIM((RTRIM(IN_Main.PartNo)) +'B'),
'',
1,
1,
GETDATE()
FROM IN_Main, AA_IN_OrderUnits
WHERE IN_Main.InventoryID = AA_IN_OrderUnits.InventoryID
Scott Pearce
Senior Analyst/Programmer
Jiwa Financials
User avatar
Scott.Pearce
Senpai
Senpai
 
Posts: 742
Joined: Tue Feb 12, 2008 11:27 am
Location: New South Wales, Australia
Topics Solved: 221

Re: Unable to insert records into IN_UnitOfMeasure

Postby Atronics » Mon Mar 27, 2017 9:40 am

Thanks, Scott.
Atronics
Frequent Contributor
Frequent Contributor
 
Posts: 119
Joined: Fri Feb 29, 2008 4:40 pm
Topics Solved: 10

Re: Unable to insert records into IN_UnitOfMeasure

Postby Mike.Sheen » Thu Apr 06, 2017 7:13 pm

FYI - I notice your join is using a non-ANSI join syntax:

Atronics wrote:FROM IN_Main, AA_IN_OrderUnits
WHERE IN_Main.InventoryID = AA_IN_OrderUnits.InventoryID


I believe at some point such joins won't be supported anymore (I think I read that in the SQL 2005 release notes - so who knows when they'll stop supporting it if it still works today).

The ANSI join would be:

Code: Select all
FROM IN_Main
JOIN AA_IN_OrderUnits ON IN_Main.InventoryID = AA_IN_OrderUnits.InventoryID


It's probably best to get into the habit of using ANSI joins sooner rather than later.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755


Return to Microsoft SQL Server

Who is online

Users browsing this forum: No registered users and 1 guest

cron