Importing records using LS

HCL Domino Domino Forum
1

I give up and hope someone can see why my script is not creating a new document with the information I am retrieving from a SQL database. For testing I am only trying to get 1 document. I am getting no error but also no document. The script is an agent that I will put under an action button to kick it off. Any eyes are appreciated. Thank you all!

Below is my script:

Sub Initialize

Dim con As New ODBCConnection

Dim qry As New ODBCQuery

Dim res As New ODBCResultSet

Dim ws As New Notesuiworkspace

Dim uidoc As Notesuidocument

Dim datasource As String

Dim resultCon As Variant

Dim sess As New NotesSession

Dim db As NotesDatabase

Dim doc As NotesDocument

Set db = sess.CurrentDatabase

datasource = “db.server/WAREHOUSE”

resultCon = con.ConnectTo(datasource)

Set uidoc = ws.CurrentDocument

If con.ConnectTo(“WAREHOUSE”, “”, “”) Then

Set qry.Connection = con

qry.SQL = “SELECT ownername, notebal, acctnbr from wh_acctcommon where acctnbr = ‘111’”

Set res.Query = qry

res.Execute

If res.IsResultSetAvailable = True Then

Do

res.FirstRow

Set doc = db.CreateDocument

With doc

.Form = "Main"

.Name = res.GetValue("Ownername")

.Acct = res.GetValue("AcctNbr")

.Save True,False

End With

			

Loop Until res.IsEndOfData

End If

res.Close(DB_CLOSE)

con.Disconnect

Else

Messagebox(“Could not connect to database server”)

End If

End Sub

2

Subject: Importing records using LS

What happens when you step through the script with the debugger turned on? Does your logic flow as you’d expect it to?

Also, is your datasource variable resolving to the value you expect? Ditto for the other object variables.

3

Subject: Importing records using LS

Cindy,

datasource = “db.server/WAREHOUSE”

resultCon = con.ConnectTo(datasource)

If con.ConnectTo(“WAREHOUSE”, “”, “”) Then

It looks like you are trying to connect to the SQL datasource on your notes DB server.

You may be running SQL on that box but you will need to use the FQDN of that host for the connection.

Try this:

’ You needd to be able to ping server.domain.com

datasource = “server.domain.com”

resultCon = con.ConnectTo(datasource)

If con.ConnectTo(“WAREHOUSE”, “”, “”) Then

Shawn

4

Subject: Importing records using LS

Cindy, The first thing to do is to add some error handling. the first line in any subroutine/function should be: on error…

Also you need to add error checking for the ODBC calls, so after the con.connectto have:

if con.geterror <> DBstsSuccess then

msgbox con.getextendederrormessage, , con.geterrormessage

exit sub ’ or some other handler

end if

also do this after res.execute, but as:

if res.geterror <> DBstsSuccess then

msgbox res.getextendederrormessage, , res.geterrormessage

exit sub ’ or some other handler

end if

This will give a clearer indication of any errors.

5

Subject: RE: Importing records using LS

I want to thank you all for your responses, however, I put in error handler and nothing, I walked through the debugger and everything is flowing. It even walks through the part to make the new document.

I use the SQL in numerous forms to go out to the SQL database and bring data into an existing form while the user is in the form.

You are not going to believe it, I found my documents, they were not in text format they were in numeric format. Sorry for all the trouble.