B4J Question [SOLVED]Problem saving with uniqueidentifier on MSSQL

[Peter Lewis]

Hi all,

I have been trying to save a record with 2 of the fields being uniqueidentifier on MSSQL.

The error I have been getting is

com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting from a character string to uniqueidentifier.

the data comes out of a MAP

paramsInfo(0) =info.Get("masteritem")

The full sub is

Sub saveiteminfo(sql As SQL,info As Map)
    
    Dim querySaveInfo As String = utils.prepareInsertQuery($"
                    INSERT INTO assets (
                           [id]
                          ,[customer_id]
                          ,[insurance_company_id]
                          ,[item_name]
                          ,[item_description]
                          ,[item_cost]
                          ,[policy_number]
                          ,[serial_number]
                          ,[claim_number]
                          ,[brand]
                          ,[extra_info]
                    )
                "$)
    
    Dim paramsInfo(querySaveInfo.Length - querySaveInfo.Replace("?","").Length) As String
    paramsInfo(0) =info.Get("masteritem")
    paramsInfo(1) =info.Get("user_id")
    paramsInfo(2) =info.Get("insurancecomany")
    paramsInfo(3) =info.Get("model")
    paramsInfo(4) =info.Get("description")
    paramsInfo(5) =0
    paramsInfo(6) =info.Get("policynumber")
    paramsInfo(7) =info.Get("serial")
    paramsInfo(8) =""
    paramsInfo(9) =info.Get("brand")
    paramsInfo(10) =""
    sql.ExecNonQuery2(querySaveInfo,paramsInfo)
    sql.close

End Sub

Have tried to change it to Object instead of string but still the same issue

 

[Jmu5667]

You should really show the full SQL insert statement as your posted code is not very helpful. What are the values your are trying to save ? If you have the full SQL statement you can use SQL Management console to exec the query to debug it.

 

[Peter Lewis]

You should really show the full SQL insert statement as your posted code is not very helpful. What are the values your are trying to save ? If you have the full SQL statement you can use SQL Management console to exec the query to debug it.

I did post the full insert statement. Please explain why you do not think it is the full code ? It is the full code in my program for insert.

Sub saveiteminfo(sql As SQL,info As Map)

Dim querySaveInfo As String = utils.prepareInsertQuery($"
INSERT INTO assets (
[id]
,[customer_id]
,[insurance_company_id]
,[item_name]
,[item_description]
,[item_cost]
,[policy_number]
,[serial_number]
,[claim_number]
,[brand]
,[extra_info]
)
"$)

Dim paramsInfo(querySaveInfo.Length - querySaveInfo.Replace("?","").Length) As String
paramsInfo(0) =info.Get("masteritem")
paramsInfo(1) =info.Get("user_id")
paramsInfo(2) =info.Get("insurancecomany")
paramsInfo(3) =info.Get("model")
paramsInfo(4) =info.Get("description")
paramsInfo(5) =0
paramsInfo(6) =info.Get("policynumber")
paramsInfo(7) =info.Get("serial")
paramsInfo(8) =""
paramsInfo(9) =info.Get("brand")
paramsInfo(10) =""
sql.ExecNonQuery2(querySaveInfo,paramsInfo)
sql.close

End Sub

As an example , here are the keys and values

Key: user_id
Value: 95AC6CBD-1640-41F5-9727-393EFC97ADD9
Key: masteritem
Value: F33EFE3D-54CF-988D-9A38-364D051BC966
Key: brand
Value: hg
Key: model
Value: gg
Key: serial
Value: hh
Key: insurancecomany
Value: gg

 

[Peter Lewis]

This is the code i used in smss to test and it works there

  INSERT INTO dbo.assets
  ([id], [customer_id],[item_name])
VALUES
  ('95AC6CBD-1640-41F5-9727-393EFC97ADD9',
   'F33EFE3D-54CF-988D-9A38-364D051BC966',
   'test' );

 

[Jeffrey Cameron]

The error you're getting seems to indicate that you are not passing a valid string that can be turned into a unique identifier as expected by the server:

A column or local variable of uniqueidentifier data type can be initialized to a value in the following ways:

• By using the NEWID or NEWSEQUENTIALID functions.
• By converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value.

I'd add a log statement and see exactly what you're passing as parameters. Unless you have a real need for unique id type, I'd stick to INT or BIGINT as your key values. See: Primary Keys: IDs versus GUIDs (codinghorror.com)