Web based applications pulls false result from SQL table

400 pts.
Tags:
Lotus Notes Forms
SQL Query
I've a web based application which when executed via the Notes form returns a false result set. The SQL query executes and returns the proper result set when run through query manager. Any help would be appreciated. The agent is called via a button executing the tools run macro command: Option Public Uselsx "*LSXODBC" Use "ZScript" Const TDefH = " " Const TDefHX = " " Const TDef = " " Const TDefX = " " Const TDefR= "
" Const TDefRX= "
" Sub Initialize   Dim session As New NotesSession Dim db As NotesDatabase Dim con As ODBCConnection Dim qry As ODBCQuery Dim result As ODBCResultSet Set db = session.CurrentDatabase Set doc = session.documentcontext Dim CVQry As String Dim RTFData As NotesRichTextItem ' Branch = Cdat(doc.Input1(0)) Branch="SE" Set con = New ODBCConnection Set qry = New ODBCQuery Set result = New ODBCResultSet Set qry.Connection = con Set result.Query = qry Set RTFData = New NotesRichTextItem(doc, "RTF") Dim CVConType As String Dim CVConSrv As String Dim CVConUid As String Dim CVConPwd As String Call CVLogin (CVConType, CVConSrv, CVConUid, CVConPwd) st = con.ConnectTo (CVConType, CVConUid, CVConPwd) If Not con.IsConnected Then Messagebox "No connection made" Else Messagebox "We are connected MF" End If CVQry = " Select bl.service_branch_code, c.first_name, c.last_name, c.title, c.phone, c.email, b.bus_code, bl.location_name, bl.addr1, bl.addr2, bl.addr3, bl.addr4, " CVQry = CVQry + " bl.city, bl.state_code, bl.postal_code " CVQry = CVQry + " From CVMicroProd.dbo.contact c " CVQry = CVQry + " Join CVMicroProd.dbo.business_location bl On c.business_id = bl.business_id And c.location_no = bl.location_no " CVQry = CVQry + " Join CVMicroProd.dbo.business_location bl On c.business_id = bl.business_id " CVQry = CVQry + " Join CVMicroProd.dbo.business b On b.business_id = bl.business_id " CVQry = CVQry + " where bl.service_branch_code = ('" + Branch + "') " CVQry = CVQry + " order by bl.service_branch_code " qry.SQL = CVQry Call RTFData.AppendText(CVQry) Call RTFData.AppendText("[ ")Call RTFData.AppendText("") Call RTFData.AppendText(TDefH + "Branch/Region" + TDefHX) Call RTFData.AppendText(TDefH + "First Name" + TDefHX) Call RTFData.AppendText(TDefH + "Last Name" + TDefHX) Call RTFData.AppendText(TDefH + "Title " + TDefHX) Call RTFData.AppendText(TDefH + "Phone" + TDefHX) Call RTFData.AppendText(TDefH + "Email" + TDefHX) Call RTFData.AppendText(TDefH + "Customer #" + TDefHX) Call RTFData.AppendText(TDefH + "Customer" + TDefHX) Call RTFData.AppendText(TDefH + "Addr1" + TDefHX) Call RTFData.AppendText(TDefH + "Addr2" + TDefHX) Call RTFData.AppendText(TDefH + "Addr3" + TDefHX) Call RTFData.AppendText(TDefH + "Addr4" + TDefHX) Call RTFData.AppendText(TDefH + "City" + TDefHX) Call RTFData.AppendText(TDefH + "State" + TDefHX) Call RTFData.AppendText(TDefH + "Zip" + TDefHX) Call RTFData.AppendText("") result.Execute Call RTFData.AppendText("") Do result.NextRow If CStr(result.GetValue(1)) "" Then Call RTFData.AppendText(TDef & result.GetValue(1) & TDefX) Else Call RTFData.AppendText(TDef & " - " & TDefX) If CStr(result.GetValue(2)) "" Then Call RTFData.AppendText(TDef & result.GetValue(2) & TDefX) Else Call RTFData.AppendText(TDef & " - " & TDefX) If CStr(result.GetValue(3)) "" Then Call RTFData.AppendText(TDef & result.GetValue(3) & TDefX) Else Call RTFData.AppendText(TDef & " - " & TDefX) If CStr(result.GetValue(4)) "" Then Call RTFData.AppendText(TDef & result.GetValue(4) & TDefX) Else Call RTFData.AppendText(TDef & " - " & TDefX) If CStr(result.GetValue(5)) "" Then Call RTFData.AppendText(TDef & result.GetValue(5) & TDefX) Else Call RTFData.AppendText(TDef & " - " & TDefX) If CStr(result.GetValue(6)) "" Then Call RTFData.AppendText(TDef & result.GetValue(6) & TDefX) Else Call RTFData.AppendText(TDef & " - " & TDefX) If CStr(result.GetValue(7)) "" Then Call RTFData.AppendText(TDef & result.GetValue(7) & TDefX) Else Call RTFData.AppendText(TDef & " - " & TDefX) If CStr(result.GetValue(8)) "" Then Call RTFData.AppendText(TDef & result.GetValue(8) & TDefX) Else Call RTFData.AppendText(TDef & " - " & TDefX) If CStr(result.GetValue(9)) "" Then Call RTFData.AppendText(TDef & result.GetValue(9) & TDefX) Else Call RTFData.AppendText(TDef & " - " & TDefX) If CStr(result.GetValue(10)) "" Then Call RTFData.AppendText(TDef & result.GetValue(10) & TDefX) Else Call RTFData.AppendText(TDef & " - " & TDefX) If CStr(result.GetValue(11)) "" Then Call RTFData.AppendText(TDef & result.GetValue(11) & TDefX) Else Call RTFData.AppendText(TDef & " - " & TDefX) If CStr(result.GetValue(12)) "" Then Call RTFData.AppendText(TDef & result.GetValue(12) & TDefX) Else Call RTFData.AppendText(TDef & " - " & TDefX) If CStr(result.GetValue(13)) "" Then Call RTFData.AppendText(TDef & result.GetValue(13) & TDefX) Else Call RTFData.AppendText(TDef & " - " & TDefX) If CStr(result.GetValue(14)) "" Then Call RTFData.AppendText(TDef & result.GetValue(14) & TDefX) Else Call RTFData.AppendText(TDef & " - " & TDefX) If CStr(result.GetValue(15)) "" Then Call RTFData.AppendText(TDef & result.GetValue(15) & TDefX) Else Call RTFData.AppendText(TDef & " - " & TDefX) Call RTFData.AppendText(" ") Loop Until result.IsEndOfData Call RTFData.AppendText("]") Call doc.save(True,True) End Sub

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • ToddN2000
    Is it possible your SQL manager may be pointing to a different datasource?Is there only one instance of your database ?
    15,605 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following