Query does not insert record when called from javascript but does from query analyzer

Question

Query does not insert record when called from javascript but does from query analyzer

dale 34292

Grasshopper

Points: 18
More actions
July 15, 2013 at 10:30 am

#395142

I have a javascript that uses a sql.execute command to execute a sql insert. The return values show success but no record is inserted. When I insert that exact command in query analyzer, it runs and inserts the record.
There are no triggers for this table.
A similar command to insert a record in a different table in the same script executes with no problem and successfully inserts the record, so I don't think it is a permissions issue.
The sql.execute command generates this sql:
Insert into fadata.dbo.FAImportInstanceData ([importinstancekey],[assettypekey],[AssetID], [Description], [DateAcquired],[OriginalCost],[UDF3Value],[UDF7Value], [UDF8Value], [UDF9Value], [UDF10Value])Values(15,5,'TLD00398','HP LJ Enterprise 500 MFP Printer','5/15/2013',1299.5,'NIC','89','KS Corporate Office-Olathe','KS Corporate Office-Olathe','KS Corporate Office-Olathe');
Any ideas what I might be doing wrong or other things to check?

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

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

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

dale 34292 (7/15/2013)
I have a javascript that uses a sql.execute command to execute a sql insert. The return values show success but no record is inserted. When I insert that exact command in query analyzer, it runs and inserts the record.
There are no triggers for this table.
A similar command to insert a record in a different table in the same script executes with no problem and successfully inserts the record, so I don't think it is a permissions issue.
The sql.execute command generates this sql:
Insert into fadata.dbo.FAImportInstanceData ([importinstancekey],[assettypekey],[AssetID], [Description], [DateAcquired],[OriginalCost],[UDF3Value],[UDF7Value], [UDF8Value], [UDF9Value], [UDF10Value])Values(15,5,'TLD00398','HP LJ Enterprise 500 MFP Printer','5/15/2013',1299.5,'NIC','89','KS Corporate Office-Olathe','KS Corporate Office-Olathe','KS Corporate Office-Olathe');
Any ideas what I might be doing wrong or other things to check?

Have you turned on profiler to make sure the statement in actually executing?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

dale 34292 Grasshopper Points: 18 More actions · Answer 2

I'll try that, although I have not used it before. However, I am pretty sure it is executing as the log from the script says that it is receiving return values:

Here is the script language:

if (!hostDB.execute(sql))

{

debug.log("DEBUG","Could not update FAImportinstancedata error: %s %s, minor code: %s %s",hostDB.majorErrorCode(),hostDB.majorErrorMessage(),hostDB.minorErrorCode(),hostDB.minorErrorMessage());

}

else

{

debug.log("DEBUG","Asset: %s added to FAImportInstanceData errors: %s, %s, %s, %s using command: %s",ASSETID,hostDB.majorErrorCode(),hostDB.majorErrorMessage(),hostDB.minorErrorCode(),hostDB.minorErrorMessage(),sql);

}

and the log entry indicates that the "else" statement is the one that executes:

07/15 11:05:46.154 FAM_Import:1C [ DEBUG ] Asset: TLD00398 added to FAImportInstanceData errors: 0, , 0, using command: Insert into fadata.dbo.FAImportInstanceData ([importinstancekey],[assettypekey],[AssetID], [Description], [DateAcquired],[OriginalCost],[UDF3Value],[UDF7Value], [UDF8Value], [UDF9Value], [UDF10Value])Values(15,5,'TLD00398','HP LJ Enterprise 500 MFP Printer','5/15/2013',1299.5,'NIC','89','KS Corporate Office-Olathe','KS Corporate Office-Olathe','KS Corporate Office-Olathe');

Thanks for the help

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

dale 34292 (7/15/2013)
I'll try that, although I have not used it before. However, I am pretty sure it is executing as the log from the script says that it is receiving return values:
Here is the script language:
if (!hostDB.execute(sql))
{
debug.log("DEBUG","Could not update FAImportinstancedata error: %s %s, minor code: %s %s",hostDB.majorErrorCode(),hostDB.majorErrorMessage(),hostDB.minorErrorCode(),hostDB.minorErrorMessage());
}
else
{
debug.log("DEBUG","Asset: %s added to FAImportInstanceData errors: %s, %s, %s, %s using command: %s",ASSETID,hostDB.majorErrorCode(),hostDB.majorErrorMessage(),hostDB.minorErrorCode(),hostDB.minorErrorMessage(),sql);
}
and the log entry indicates that the "else" statement is the one that executes:
07/15 11:05:46.154 FAM_Import:1C [ DEBUG ] Asset: TLD00398 added to FAImportInstanceData errors: 0, , 0, using command: Insert into fadata.dbo.FAImportInstanceData ([importinstancekey],[assettypekey],[AssetID], [Description], [DateAcquired],[OriginalCost],[UDF3Value],[UDF7Value], [UDF8Value], [UDF9Value], [UDF10Value])Values(15,5,'TLD00398','HP LJ Enterprise 500 MFP Printer','5/15/2013',1299.5,'NIC','89','KS Corporate Office-Olathe','KS Corporate Office-Olathe','KS Corporate Office-Olathe');
Thanks for the help

I have no idea what this javascript part is but it does look like the execute() method returned true. If the data is not in your database you should run profiler and see if it actually runs.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/