Update_recordset throwing Error converting data type nvarchar to bigint.

(0) Share

Report

Posted on by Wekey

302

Hello Everyone,

I am getting this strange error when I am using update_recordset to update the table, Kindly help me what I am missing.

update_recordset tablename
            setting 
                stringfieldname = parameterTable.stringField   tablename2.stringfield   ","
            join tablename2
                where tablename2.stringField2 == tablename.stringfieldname;

The strange part is it is working when I remove "," in the setting statement but if I add any string it is throwing me the following error,

session 5120 (Admin)
UPDATE T1 SET stringfieldname =((? T2.stringfield) ?),RECVERSION=? FROM tablename T1 CROSS JOIN tablename2 T2 WHERE (T1.PARTITION=5637144576) AND ((T2.PARTITION=5637144576) AND (T2.stringfield2=T1.stringfieldname))
[Microsoft[ODBC Driver 17 for SQL Server[SQL ServerError converting data type nvarchar to bigint.
Object Server Azure:
Cannot edit a record in table name (tablename). Reference: 0. The SQL database has issued an error.

PS: I have already tried full db sync. please help me what I am doing wrong.

I have the same question (0)

All responses (14)

Answers (0)

Wekey 302 on at

Like (0)

Report
Another strange thing is even the below code works,

update_recordset tablename setting stringfieldname = parameterTable.stringField tablename2.stringfield join tablename2 where tablename2.stringField2 == tablename.stringfieldname; update_recordset tablename setting stringfieldname = tablename.stringfieldname ',' join tablename2 where tablename2.stringField2 == tablename.stringfieldname;

But it is going to kill the performance updating the same field twice just for adding a new string value which can be performed in a single update_recordset. Just for the clarity.

Was this reply helpful? Yes No
André Arnaud de Cal... 300,911 Super User 2025 Season 2 on at

Like (0)

Report
Hi Wekey,

Have you tried also something like the next approach?

private str concatedString = parameterTable.stringField tablename2.stringfield ","; update_recordset tablename setting stringfieldname = concatedString join tablename2 where tablename2.stringField2 == tablename.stringfieldname;

Was this reply helpful? Yes No
Wekey 302 on at

Like (0)

Report

Hello Andre,

Thanks for the reply.

I will not be able to do this as the tablename2.stringfield value will not be available. tablename2 is the table I am using in join and the value will be fetched only in the join.

Thanks

Was this reply helpful? Yes No
Deepak Agarwal 8,598 on at

Like (0)

Report

Hi,

Can you share complete code, how you are getting buffer for parameterTable. can you try to add parameterTable in same query.

Was this reply helpful? Yes No
Wekey 302 on at

Like (0)

Report
Hello Deepak,

please find the entire code,

Table2 tablename2; Table tablename; ParameterTable parameterTable = ParameterTable::find(); update_recordset tablename setting stringfieldname = parameterTable.stringField tablename2.stringfield "," join tablename2 where tablename2.stringField2 == tablename.stringfieldname;

I will not be able to add in the query because they have no relations, and it is a parameter table hence I thought we can use find method to fetch the values.

TIA

Was this reply helpful? Yes No
Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report

Can you confirm if these fields are strings? And are these synced successfully to database? Are you able to update the same value from backend database?

Was this reply helpful? Yes No
Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report

Also can you confirm if you are getting the parameter table buffer as I am not able to see the value in that SQL error.

Was this reply helpful? Yes No
Wekey 302 on at

Like (0)

Report

Hello Bharani,

Yeah, all the fields are string field, and it is synced to DB successfully. The same value can be updated in backend using SQL. And I am getting the parameter value which is also a string field (Hence It is really matter that it needs a value? However, the value is coming)

Thanks

Was this reply helpful? Yes No
Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report

Have you tried giving setting tablename.fieldname = "thisvalue"? instead of a stringfieldname?

Also have you tried with simple update statement?

Was this reply helpful? Yes No
Wekey 302 on at

Like (0)

Report
Hello Bharani,

Yeah, I have tried the following still no luck,

Table2 tablename2; Table tablename; ParameterTable parameterTable = ParameterTable::find(); update_recordset tablename setting stringfieldname = "stringValue" tablename2.stringfield "," join tablename2 where tablename2.stringField2 == tablename.stringfieldname;

However, when I tried the following code, it is working without throwing an error, but I need to update the table name and not a constant string value,

Table2 tablename2; Table tablename; ParameterTable parameterTable = ParameterTable::find(); update_recordset tablename setting stringfieldname = "stringValue" "stringvalue1" "," join tablename2 where tablename2.stringField2 == tablename.stringfieldname;

Thanks

Was this reply helpful? Yes No