How to debug “unterminated string constant” in VBScript?

Question

I am getting:

Line 32, char 16 , unterminated string constant, code :800A0409 error.

How can I debug this? I have created a VB Script for the query reports in ALM and I will be scheduling it in Windows Scheduler.

Is there any other optimized way of achieving the goal?

Reference http://eyeontesting.com/questions/2159/sql-queries-in-api.html

Sub Query()

Dim qcServer, qcDomain, qcProject, qcUser, qcPassword, sSql

     qcServer = "XXXXXXXXXXX"
     qcDomain = "XXXXXXXXXXX"
     qcProject = "XXXXXXX"
     qcUser = "XXXXXXXX"
     qcPassword = "XXXXXXX"


 Set tdc = CreateObject("tdapiole80.tdconnection")

 'Check to see that the tdc object exists
 If tdc Is Nothing Then
 MsgBox "The tdc object is empty"
 End If

 'Establish the connection and log in
 tdc.InitConnectionEx qcServer
 tdc.Login qcUser, qcPassword
 tdc.Connect qcDomain, qcProject


 'Create the tdc Command Object
 Set oCommand = tdc.Command

 'Build the query

 sSql = "SELECT
TEST.TS_NAME AS "Test Case Name",
TESTCYCL.TC_STATUS as "Test Case Status",
CYCL_FOLD.CF_ITEM_NAME as "Test Set Folder Name",
--CYCL_FOLD.CF_ITEM_ID as "Folder ID",
CYCL_FOLD.CF_ITEM_PATH AS "Folder Path",
TESTCYCL.TC_USER_01 as 'ExternalDefectID',
TESTCYCL.TC_EXEC_DATE  AS 'Execution Date',
TESTCYCL.TC_ACTUAL_TESTER AS   'Tester'


FROM
CYCL_FOLD LEFT OUTER JOIN CYCLE ON CYCL_FOLD.CF_ITEM_ID = CYCLE.CY_FOLDER_ID
LEFT OUTER JOIN TESTCYCL ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
LEFT JOIN TEST ON TEST.TS_TEST_ID = TESTCYCL.TC_TEST_ID

WHERE
CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAASAAHAADAAAAAAA%' /*Folder Path ID*/

ORDER BY
CF_ITEM_NAME"

 'Set the SQL command to the Test Coverage query
 oCommand.CommandText = sSql

 'Execute the query and store in the SQLResults resultset.
  Set SQLResults = oCommand.Execute

 'Prepare the worksheet
 Worksheets("Sheet1").Range("A:G").ClearContents

 'Set the header row titles.
 Worksheets("Sheet1").Range("A1") = "Test Case Name"
 Worksheets("Sheet1").Range("B1") = "Test Case Status"
 Worksheets("Sheet1").Range("C1") = "Test Set Folder Name"
 Worksheets("Sheet1").Range("D1") = "Folder Path"
 Worksheets("Sheet1").Range("E1") = "ExternalDefectID"
 Worksheets("Sheet1").Range("F1") = "Execution Date"
 Worksheets("Sheet1").Range("G1") = "Tester"


 'Start populating data on row 2 (leaving the header information above).
 iExcelRow = 2

 'Iterate through the query results and populate the worksheet.
 For iRecord = 1 To SQLResults.RecordCount

     'Write the values to the worksheet
      Worksheets("Sheet1").Range("A" & iExcelRow) = SQLResults.FieldValue("Test Case Name")
      Worksheets("Sheet1").Range("B" & iExcelRow) = SQLResults.FieldValue("Test Case Status")
      Worksheets("Sheet1").Range("C" & iExcelRow) = SQLResults.FieldValue("Test Set Folder Name")
      Worksheets("Sheet1").Range("D" & iExcelRow) = SQLResults.FieldValue("Folder Path")
      Worksheets("Sheet1").Range("E" & iExcelRow) = SQLResults.FieldValue("ExternalDefectID")
      Worksheets("Sheet1").Range("F" & iExcelRow) = SQLResults.FieldValue("Execution Date")
      Worksheets("Sheet1").Range("G" & iExcelRow) = SQLResults.FieldValue("Tester")

     'Increment the iteration
      iExcelRow = iExcelRow + 1
      SQLResults.Next
 Next


 'Disconnect from Quality Center
 If tdc.Connected = True Then
 tdc.Disconnect
 End If

 'Log off the server
 If tdc.LoggedIn Then
 tdc.Logout
 End If

 'Release the TDConnection object.
 tdc.ReleaseConnection

 'Adjust the column width
 Worksheets("Sheet1").Columns("A1:G1").EntireColumn.AutoFit

 Set SQLResults = Nothing
 Set oCommand = Nothing
 Set tdc = Nothing

 MsgBox "Done"

 End Sub

There was a supplementary question here that I have removed, in order to avoid closing the question as "too broad". — halfer
Recommended reading: questions that ask "please help me" tend to be looking for highly localized guidance, or in some cases, ongoing or private assistance, which is not suited to our Q&A format. It is also rather vague, and is better replaced with a more specific question. Please read Why is “Can someone help me?” not an actual question?. — halfer

oracle certified professional oracle certified professional · Accepted Answer · 2018-02-11T19:50:30

It's probably this bit

 sSql = "SELECT
TEST.TS_NAME AS "Test Case Name",
TESTCYCL.TC_STATUS as "Test Case Status",
CYCL_FOLD.CF_ITEM_NAME as "Test Set Folder Name",
--CYCL_FOLD.CF_ITEM_ID as "Folder ID",
CYCL_FOLD.CF_ITEM_PATH AS "Folder Path",
TESTCYCL.TC_USER_01 as 'ExternalDefectID',
TESTCYCL.TC_EXEC_DATE  AS 'Execution Date',
TESTCYCL.TC_ACTUAL_TESTER AS   'Tester'


FROM
CYCL_FOLD LEFT OUTER JOIN CYCLE ON CYCL_FOLD.CF_ITEM_ID = CYCLE.CY_FOLDER_ID
LEFT OUTER JOIN TESTCYCL ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
LEFT JOIN TEST ON TEST.TS_TEST_ID = TESTCYCL.TC_TEST_ID

WHERE
CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAASAAHAADAAAAAAA%' /*Folder Path ID*/

ORDER BY
CF_ITEM_NAME"

To do multiline strings, you need to concatenate shorter strings and use the line continuation character _, and to embed double quotes in string literals you need to escape them by doubling them up:

 sSql = "SELECT " & _
"TEST.TS_NAME AS ""Test Case Name""," & _
"TESTCYCL.TC_STATUS as ""Test Case Status""," & _
"CYCL_FOLD.CF_ITEM_NAME as ""Test Set Folder Name""," & _
"--CYCL_FOLD.CF_ITEM_ID as ""Folder ID""," & _
"CYCL_FOLD.CF_ITEM_PATH AS ""Folder Path""," & _
"TESTCYCL.TC_USER_01 as ""ExternalDefectID""," & _
"TESTCYCL.TC_EXEC_DATE  AS ""Execution Date"","  & _
"TESTCYCL.TC_ACTUAL_TESTER AS   ""Tester"""  & _
"FROM " & _
"CYCL_FOLD LEFT OUTER JOIN CYCLE ON CYCL_FOLD.CF_ITEM_ID = CYCLE.CY_FOLDER_ID " & _
"LEFT OUTER JOIN TESTCYCL ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID " & _
"LEFT JOIN TEST ON TEST.TS_TEST_ID = TESTCYCL.TC_TEST_ID " & _

"WHERE " & _
"CYCL_FOLD.CF_ITEM_PATH LIKE 'AAAAASAAHAADAAAAAAA%' /*Folder Path ID*/ " & _

"ORDER BY " & _
"CF_ITEM_NAME"

How to debug “unterminated string constant” in VBScript?

1 Answers