ChrisMinas
asked on
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
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
ASKER
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
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
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
eg;
result.GetValue("cxmTitle"
msgbox cxmTitle
ASKER
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
I made the following change to my code:
If (result.GetError = DBstsSUCCESS) Then
Dim cxmTitle As String
cxmTitle = result.GetValue("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
replace this line
cxmTitle = result.GetValue("cxmTitle" , cxmTitle)
to this
call result.GetValue("cxmTitle" , cxmTitle)
cxmTitle = result.GetValue("cxmTitle"
to this
call result.GetValue("cxmTitle"
ASKER
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
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
ASKER
Hello.
Check this site out:
http://www-10.lotus.com/ldd/nd6forum.nsf/DateAllThreadedWeb/B00FA444132593D785256F5000157DAB?opendocument&login
Chris
Check this site out:
http://www-10.lotus.com/ldd/nd6forum.nsf/DateAllThreadedWeb/B00FA444132593D785256F5000157DAB?opendocument&login
Chris
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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