B4J Question [SOLVED]Problem saving with uniqueidentifier on MSSQL

Peter Lewis

Active Member
Licensed User
Longtime User
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
B4X:
paramsInfo(0) =info.Get("masteritem")

The full sub is

B4X:
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

Well-Known Member
Licensed User
Longtime User
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.
 
Upvote 0

Peter Lewis

Active Member
Licensed User
Longtime User
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
 
Last edited:
Upvote 0

Peter Lewis

Active Member
Licensed User
Longtime User
This is the code i used in smss to test and it works there

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


1627569134030.png
 
Upvote 0

Jeffrey Cameron

Well-Known Member
Licensed User
Longtime User
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)
 
Upvote 0
Top