Problem with LSXODBC Insert

HCL Domino Domino Forum
1

I’m having trouble adding new rows to this table when there are existing rows. If I drop the entire table first I can loop through and add all records successfully, but when I condition the DELETE so there are records left in the table the agent errors out when it gets to the result.updaterow.

Has anyone else run into this problem and/or know a solution?

qry.SQL = “DELETE FROM Ticket_ARMS where Ticket_ID is not null”

result.Execute

qry.SQL = “SELECT * FROM Ticket_ARMS”

result.Execute

Set doc = view.GetFirstDocument

While Not(doc Is Nothing)

result.AddRow

Call result.SetValue(“DocID”,doc.universalid)

		Call result.SetValue("SSN",Left$(doc.SocialSecurityNumber(0),9))

		Call result.SetValue("RequestNumber",Left$(doc.RequestNumber(0),8))

		Call result.SetValue("LastName",Left$(doc.lastname(0),30))

		Call result.SetValue("FirstName",Left$(doc.firstname(0),20))

		Call result.SetValue("MiddleName",Left$(doc.middlename(0),20))

		Call result.SetValue("ManagerName",Left$(doc.managername_1(0),50))

		Call result.SetValue("NewUserID",Left$(doc.newuserid(0),20))

		Call result.SetValue("DeleteAccess",Left$(doc.deleteaccs(0),20))

		Call result.SetValue("AddAccess",Left$(doc.addaccs(0),20))

		Call result.SetValue("Transfer",Left$(doc.transfer(0),20))

		Call result.SetValue("NameChange",Left$(doc.namechange(0),20))

		Call result.SetValue("PreviousName",Left$(doc.previousname(0),20))

		Call result.SetValue("UserIDAssigned",Left$(doc.useridassigned(0),8))

		Call result.SetValue("RetailName",Left$(doc.retailname(0),50))

		Call result.SetValue("RetailBank",Left$(doc.retailbank(0),10))

		Call result.SetValue("RetailBranch",Left$(doc.retailbranch(0),10))

		Call result.SetValue("NonRetailModel",Left$(doc.nonretailmodel(0),50))

		Call result.SetValue("LotusBankNum",Left$(doc.lotusbanknum(0),10))

		Call result.SetValue("LotusBranchNum",Left$(doc.lotusbranchnum(0),10))

		Call result.SetValue("LotusExpDate",Left$(doc.lotusexpdate(0),10))

		Call result.SetValue("INotesBankNum",Left$(doc.inotesbanknum(0),10))

		Call result.SetValue("INotesBranchNum",Left$(doc.inotesbranchnum(0),10))

		Call result.SetValue("RequestType",Left$(doc.requesttype(0),50))

result.UpdateRow

Set doc = view.GetNextDocument(doc)

Wend

result.Close(DB_CLOSE)

con.Disconnect

2

Subject: Problem with LSXODBC Insert

Andrew

what is the error message that you are getting?

after you do the insert print the error message as in

print result.getExendedErrorMessage

This will tell you what went wrong.

john

3

Subject: RE: Problem with LSXODBC Insert

Sorry, I should’ve mentioned that I tried that and all I got was “LS:DO- ODBC could not complete the requested operation.”

4

Subject: RE: Problem with LSXODBC Insert

That was the message you got when you added in the statement I asked you to put in?

Also hat are you connecting to?

I have conected to excel, access, oracle and even notes. Never had that issue, but the extendederrormessage should have told you more than that.

You may have to play with the result settings, or I think I had to do something funcky with the addRow.

John

5

Subject: RE: Problem with LSXODBC Insert

I am connecting to a SQL Server db.

I found the problem. I added Result.NextRow immediately before Result.AddRow and that allowed me to insert after the existing rows. Thanks for responding, John.

Drew

6

Subject: Problem with LSXODBC Insert - Simular problem

Hi, AndrewI have a problem like you had.

Im am adding new rows to a sql-db on a Microsoft SQL-server. I am using LS ODBC-classes.

Adding new rows is no problem. Rows are built from a notes document. When I want to “export” a notes again (a row from that notes document already exists), I get the LS:DO-ODBC error: ODBC could not complete …etc.

When I manually delete that row from the sql-table, I can add a new row from that Notes document successfully.

Here is some code:

Dim (declare ODBC-variables)

qry.SQL=“SELECT * from table”

set result.query=qry

result.CacheLimit=DB_NONE

result.Execute

result.LastRow

result.AddRow

call result.SetValue()

result.UpdateRow

So, when this row already exists, the operation failes.

I hope you, or someone else, can help me.

Koos van Harmelen

7

Subject: Problem with LSXODBC Insert - Simular problem

Hi, AndrewI have a problem like you had.

Im am adding new rows to a sql-db on a Microsoft SQL-server. I am using LS ODBC-classes.

Adding new rows is no problem. Rows are built from a notes document. When I want to “export” a notes again (a row from that notes document already exists), I get the LS:DO-ODBC error: ODBC could not complete …etc.

When I manually delete that row from the sql-table, I can add a new row from tah Notes document successfully.

I hope you, or someone else, can help me.

Koos van Harmelen

8

Subject: RE: Problem with LSXODBC Insert

Glad you found it. hopefully when i said something funcy about addrow, that helped you out. now that you mention it, that is what I did also - just glad you got it solved. good luck.

9

Subject: RE: Problem with LSXODBC Insert

Yes, your addrow suggestion jogged my memory just right. Thanks again :slight_smile: