Result.Execute returns False

HCL Domino Domino Forum
1

Hi,

I have following code in my postopen form event. I am trying to connect to SQL server and get the listname field. I can see list of all fields and but when I tryig to get field value, it just returns false. Please let me know if anyone have any idea how to fix this.

thanks

archana

Sub Postopen(Source As Notesuidocument)

Dim con As New ODBCConnection

Dim qry As New ODBCQuery

Dim result As New ODBCResultSet

Dim session As New NotesSession

Dim db As NotesDatabase

Dim doc As NotesDocument

Set qry.Connection = con

Set result.Query = qry

qry.SQL = “SELECT ListName FROM Main”

result.Execute

If Not result.IsResultSetAvailable Then

Messagebox “Couldn’t get result set”

Exit Sub

End If

msg = “Fields in MAIN:” & Chr(10)

For i = 1 To result.NumColumns

msg = msg & Chr(10) & i & " " & result.FieldName(i)

Next

Messagebox msg, “Field names”

temp_var = result.GetValue(ListName)

Messagebox(temp_var)

result.Close(DB_CLOSE)

con.Disconnect

End Sub

2

Subject: Result.Execute returns False

For a start, you’re not actually connecting to another database. You’ll want a line like this before you start messing with qry:

Call con.ConnectTo(“My DSN”)

3

Subject: RE: Result.Execute returns False

Oh.actually I have this in my script. I forgot to copy over this before. I do get connected. Here is the actual code that I have

Sub Postopen(Source As Notesuidocument)

Dim con As New ODBCConnection

Dim qry As New ODBCQuery

Dim result As New ODBCResultSet

Dim session As New NotesSession

Dim db As NotesDatabase

Dim doc As NotesDocument

Dim fieldName As String



Set qry.Connection = con

Set result.Query = qry



If Not con.ConnectTo("test") Then

	Messagebox "Could not connect to SQL Server",, _

	"Error connecting"

	Exit Sub

End If



qry.SQL = "SELECT ListName FROM Main"

result.Execute



If Not result.IsResultSetAvailable Then

	Messagebox "Couldn't get result set"

	Exit Sub

End If

msg = "Fields in MAIN:" & Chr(10)

For i = 1 To result.NumColumns

	msg = msg & Chr(10) & i & " " & result.FieldName(i)

Next

Messagebox msg,, "Field names"

fieldName = Inputbox$("Field name?", "Main table")



temp_var = result.GetValue(ListName)

Messagebox(temp_var)



result.Close(DB_CLOSE)

con.Disconnect

End Sub

4

Subject: Your problem is the following AFAIK…

temp_var = result.GetValue(ListName)should probably be:

temp_var = result.GetValue(“ListName”)

or

temp_var = result.GetValue(fieldName)

or

temp_var = result.GetValue(1)

5

Subject: RE: Your problem is the following AFAIK…

I have tried all these possibilities …

temp_var = result.GetValue(“ListName”)

temp_var = result.GetValue(3)

still returns false

6

Subject: RE: Your problem is the following AFAIK…

temp_var = result.GetValue(3) won’t work. There’s only one column in your query, so index value other than 1 will fail. But temp_var = result.GetValue(“ListName”) should work if you’re getting results. Have you followed the code through in the debugger to make sure that the other objects are getting created correctly? Also, try some debugging code around result.Execute, like this:

If Not result.Execute Then

Print result.GetExtendedErrorMessage

End If

7

Subject: RE: Your problem is the following AFAIK…

HmmI tried temp_var = result.GetValue(1)…returns false

tried to get result.GetExtendedErrorMessage. But code does not stop at that line.

I have tried this query on SQL client and it does return values.

8

Subject: RE: Your problem is the following AFAIK…

What do you get for this line?

Messagebox msg, “Field names”

9

Subject: RE: Your problem is the following AFAIK…

it shows fieldname Listname

10

Subject: RE: Your problem is the following AFAIK…

Try calling result.FirstRow before trying to get the value.

11

Subject: RE: Your problem is the following AFAIK…

That did not work too. It is really weird…