?
Solved

Not retuening data from SQL Server 2000 after call from Notes application via LSXODBC

Posted on 2004-11-15
9
Medium Priority
?
282 Views
Last Modified: 2013-12-18
Hello.

I have written an agent in LotusScript that is to access an SQL Server 2000 database, Northwind. I have setup an ODBC and my code uses this ODBC name. My problem is that when I run my code I connect to the database and run the SQL statement, I can see, from my meesagebox that I have 4 records returned, which is correct, however when I try to display the value of the field I want, all I get is 'False'

I am at a loss as to why this happening and would appreciate if you could offer some advice as to what it is I am doing wrong.

My code is:

Option Public
Uselsx "*LSXODBC"

Sub Initialize
      
      Dim sess As New NotesSession
      Dim db As NotesDatabase
      Dim doc As Notesdocument
      Dim olddoc, configdoc As Notesdocument
      Dim view, configview As notesview
      Dim con As New ODBCConnection
      Dim qry As New ODBCQuery
      Dim result As New ODBCResultSet
      Dim GstCode As String
      Dim GstName As String
      Dim GstCat As String
      Dim msg As String
      Dim subj As String
      
      Set db = sess.CurrentDatabase
      Dim user1 As String
      Dim password As String
      Dim source As String
      Set qry.Connection = con
      
      Dim sqlStatement As String
      
      password = "password"
      user1 = "cxmAccess2"
      source = "cxmDBTest1"
      'con.ConnectTo source
      con.ConnectTo source, user1, password
      sqlStatement = "SELECT cxmTitle FROM cxmPeople"
      
      Messagebox "con error " & Cstr(con.GetError)
      
      'qry.SQL = |SELECT * FROM Employees|
      qry.SQL = sqlStatement
      
      Set result.Query = qry
      
      
      result.Execute      
      
      result.LastRow
      Dim count As Integer
      count = result.NumRows
      result.FirstRow ' Go back to first
      
      If result.NumRows <> 0 Then
            Messagebox "nbr of records returned are " & Cstr(result.NumRows)
            
            Do
                  result.NextRow
                  If (result.GetError  = DBstsSUCCESS) Then
                        Messagebox "title is " & result.GetValue("cxmTitle")
                  Else
                        Messagebox con.GetErrorMessage, 0, "ODBC Error!"      
                        Messagebox Cstr(con.GetError), 0, "1 ODBC Error!"      
                  End If
            Loop Until result.IsEndOfData
            
      Else
            Messagebox Cstr(con.GetError), 0, "2 ODBC Error!"      
      End If
      
      con.Disconnect
      
End Sub

Kind Regards
Chris Minas
0
Comment
Question by:ChrisMinas
  • 5
  • 4
9 Comments
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 12594185
To get NumRows.. you have to set pointer to lastrow...

so your block statement
count = result.NumRows
result.FirstRow ' Go back to first

becomes

result.LastRow ' Go to last
count = result.NumRows ' This will return correct count
result.FirstRow ' Reset back

and replace if condition (Basically don't use result.numrows ... rather use count)
 If result.NumRows <> 0 Then

to
If count > 0 Then

~Hemanth
0
 

Author Comment

by:ChrisMinas
ID: 12598608
Thanks Hemanth.

I am now successfully showing the nbr of records in the table, however when I attempt to display the value of a field all I get returned is 'False'.

My code now reads:

                password = "password"
      user1 = "cxmAccess2"
      source = "cxmDBTest1"
      'con.ConnectTo source
      con.ConnectTo source, user1, password
      sqlStatement = "SELECT cxmTitle FROM cxmPeople"
      
      Messagebox "con error " & Cstr(con.GetError)
      
      'qry.SQL = |SELECT * FROM Employees|
      qry.SQL = sqlStatement
      
      Set result.Query = qry
      
      
      result.Execute      
      
      result.LastRow
      Dim count As Integer
      count = result.NumRows
      result.FirstRow ' Go back to first
      
      If count <> 0 Then
            
            Do
                  result.NextRow
                  If (result.GetError  = DBstsSUCCESS) Then
                        Messagebox "title is " & result.GetValue("cxmTitle")
                  Else
                        Messagebox con.GetErrorMessage, 0, "ODBC Error!"      
                        Messagebox Cstr(con.GetError), 0, "1 ODBC Error!"      
                  End If
            Loop Until result.IsEndOfData
            
      Else
            Messagebox Cstr(con.GetError), 0, "2 ODBC Error!"      
      End If
      
      con.Disconnect



The messagebox statement:

Messagebox "title is " & result.GetValue("cxmTitle")

displays a value of 'False' for title.

Thanks in-advance for any advice.

Kind Regards
Chris Minas
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 12598687
Wrong use of the function.. you have store the value into a variable.. In your case it is returning the error code and which is false (No Error)

eg;
result.GetValue("cxmTitle", cxmTitle)
msgbox cxmTitle


0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:ChrisMinas
ID: 12598983
Hi Hemantha.

I made the following change to my code:

If (result.GetError  = DBstsSUCCESS) Then
                                                Dim cxmTitle As String
      cxmTitle = result.GetValue("cxmTitle", cxmTitle)
      Msgbox "and the value is = " & cxmTitle
Else
      Messagebox con.GetErrorMessage, 0, "ODBC Error!"
End If

The field name on the table is called cxmTitle and I am still having the value False returned.

Can it be due to the data type of my variable. I know I must be close. Thanks again for your time and effort in assisting me.

Kind Regards
Chris Minas
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 12599086
replace this line
cxmTitle = result.GetValue("cxmTitle", cxmTitle)

to this

call result.GetValue("cxmTitle", cxmTitle)
0
 

Author Comment

by:ChrisMinas
ID: 12599124
tried it and now instead of getting false, I am getting a blank value returned. The field does have a value:

If (result.GetError  = DBstsSUCCESS) Then
                                                Dim cxmTitle As String
                        
      Call result.GetValue( "cxmTitle" , cxmTitle )
      Msgbox "and the value is = " & cxmTitle
Else
      Messagebox con.GetErrorMessage, 0, "ODBC Error!"      
      Messagebox Cstr(con.GetError), 0, "1 ODBC Error!"      
End If

It's an improvement on False (:))

Kind Regards
Chris Minas

0
 
LVL 24

Accepted Solution

by:
HemanthaKumar earned 375 total points
ID: 12638797
Are you using nchar datatype ? I believe it is not supported in notes !
0
 

Author Comment

by:ChrisMinas
ID: 12640035
Hello Hemantha.

Yes I am using nchar. I have now moved away from OS:DO and I am using LSXLC instead and it works alot better i.e. I can return any data type.

The problem is I now need to get myself up to speed with the classes for LSXLC.

I did try converting the data from from nchar to text etc but with no success.

I do find using LS:DO easier than LSXLC but I am confident after some practice it will be OK.

Thanks heaps for you time and effort.

Kind Regards
Chris
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month13 days, 16 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question