Test a SQL Statement in Query Analyzer

[debbie1212]

Can I test a SQL statement in Query Analyzer without passing in the parameters? In other words, I have the following statement in my class and I want to find out what it is passing:

Public Sub UpdateAEtProjectSchedule(sConnectString , rsAEtProjectSchedule , RC , ErrorMessage )

sSQLStmt = ""
sSQLStmt = sSQLStmt & "execute AEstoUpdateAEtProjectSchedule "
sSQLStmt = sSQLStmt & "@schdkey=" & rsAEtProjectSchedule.Fields.Item("SchdKey&quot & ","
sSQLStmt = sSQLStmt & "@schdprojkey=" & rsAEtProjectSchedule.Fields.Item("SchdProjKey&quot & ","
sSQLStmt = sSQLStmt & "@schdphase=" & Replace(RTrim(rsAEtProjectSchedule.Fields.Item("SchdPhase&quot), "'", "''&quot & ","
sSQLStmt = sSQLStmt & "@schdduration=" & rsAEtProjectSchedule.Fields.Item("SchdDuration&quot & ","
sSQLStmt = sSQLStmt & "@schdstartdate=" & sDateFormat(rsAEtProjectSchedule.Fields.Item("SchdStartDate&quot) & ","
sSQLStmt = sSQLStmt & "@schdenddate=" & sDateFormat(rsAEtProjectSchedule.Fields.Item("SchdEndDate&quot)

I have the following code in my ASP page:

rsAEtProjectSchedule.Fields("SchdProjKey&quot = sKey
For nSub = 1 to ubound(sPhase)
rsAEtProjectSchedule.Fields("SchdPhase&quot = sPhase(nSub)
rsAEtProjectSchedule.Fields("SchdDuration&quot = sPhaseDuration(nSub)
rsAEtProjectSchedule.Fields("SchdStartDate&quot = sPhaseStart(nSub)
rsAEtProjectSchedule.Fields("SchdEndDate&quot = sPhaseEnd(nSub)
objProjectSchedule.UpdateAEtProjectSchedule Session("DATA_SOURCE&quot, rsAEtProjectSchedule, RC, ErrorMessage
If RC = rsReturnCodes.Fields("RC_SUCCESS&quot Then
Else
sResult = "Error encountered on Schedule Update. " & ErrorMessage
tfContinue = False
Exit For
End If
Next

I get the error message "Error encountered on Schedule Update. Line 1: Incorrect syntax near ','.

I would like to test it in the Query Analyzer to see what it is passing. I tried testing it in the web page with a Response.Write which was suggested on another thread but it didn't work right. I must have put it in the wrong place.

I know the problem is with schdPhase because of all of the quotes and commas but I need to see what exactly it is passing to correct it.

Thanks,
Debbie

 

[cbokowski]

Put a response.write right after you have built the "sSQLStmt" string.

 

[debbie1212]

I already tried that and got the same error message. This is what I put:

sSQLStmt = ""
sSQLStmt = sSQLStmt & "execute AEstoUpdateAEtProjectSchedule "
sSQLStmt = sSQLStmt & "@schdkey=" & rsAEtProjectSchedule.Fields.Item("SchdKey&quot & ","
sSQLStmt = sSQLStmt & "@schdprojkey=" & rsAEtProjectSchedule.Fields.Item("SchdProjKey&quot & ","
sSQLStmt = sSQLStmt & "@schdphase='" & Replace(RTrim(rsAEtProjectSchedule.Fields.Item("SchdPhase&quot), "'", "''&quot & "'" & ","
sSQLStmt = sSQLStmt & "@schdduration=" & rsAEtProjectSchedule.Fields.Item("SchdDuration&quot & ","
sSQLStmt = sSQLStmt & "@schdstartdate=" & sDateFormat(rsAEtProjectSchedule.Fields.Item("SchdStartDate&quot) & ","
sSQLStmt = sSQLStmt & "@schdenddate=" & sDateFormat(rsAEtProjectSchedule.Fields.Item("SchdEndDate&quot)

Response.Write (&quot;SQL = &quot; & sSQLStmt & &quot;<br>&quot