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

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
ChrisMinasAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HemanthaKumarCommented:
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
ChrisMinasAuthor Commented:
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
HemanthaKumarCommented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

ChrisMinasAuthor Commented:
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
HemanthaKumarCommented:
replace this line
cxmTitle = result.GetValue("cxmTitle", cxmTitle)

to this

call result.GetValue("cxmTitle", cxmTitle)
0
ChrisMinasAuthor Commented:
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
ChrisMinasAuthor Commented:
0
HemanthaKumarCommented:
Are you using nchar datatype ? I believe it is not supported in notes !
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ChrisMinasAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.