Export to MSSQL problem

HCL Domino Domino Forum
1

I wrote a piece of agent that export data from notes document to MSSQL. I tested this agent against MS Access and it worked fine. The problem I faced using MSSQL is that only one record is created. I didn’t define Primary key in the table so I don’t think that caused the problem. Can anyone tell me if something is wrong with this code.

Sub Initialize

'LSX Declaration and setting	

Dim con As New ODBCConnection

Dim qry As New ODBCQuery

Dim result As New ODBCResultSet

Set qry.Connection = con

Set result.Query = qry



Set session = New NotesSession

Set db = session.CurrentDatabase



serverName=db.Server

Path$=fgeneratepath("")	

Set dbArch = session.GetDatabase(serverName, Path$ +"ielsarchv.nsf")



'Get the view to process

Set view=dbArch.GetView("vwLookupByType")

Set docColl=view.GetalldocumentsbyKey("Team Formation")



Set qry.Connection = con	





Set result.Query = qry

con.ConnectTo("IELS")

'queries the table Contacts.

qry.SQL = "SELECT * FROM Team"

If docColl.Count>0 Then

	Set doc=docColl.getfirstdocument

	Do While Not (Doc Is Nothing)

		For i=0 To Ubound(doc.StudentInfoList)

			result.Execute

			result.AddRow

			Call result.SetValue("TeamID",doc.TeamID(0))

			Call result.SetValue("TeamName",doc.TeamName(0))

			Call result.SetValue("FormNo",doc.FormNo(0))

			Call result.SetValue("FormName",doc.FormName(0))

			Call result.SetValue("StudentName",fword(doc.StudentInfoList(i),"~~",2))

			Call result.SetValue("StudentID",fword(doc.StudentInfoList(i),"~~",1))

			Call result.SetValue("StudentRole",fword(doc.RoleNumber(i),"~~",1))

			Call result.SetValue("StudentPhone",fword(doc.RoleNumber(i),"~~",2))

			Call result.SetValue("DiplomaProgram",fword(doc.StudentInfoList(i),"~~",4))

			Call result.SetValue("TermNo",doc.TermNo(0))

			Call result.SetValue("TermDesc",doc.TermDesc(0))

			Call result.SetValue("AcadYear",doc.AcadYear(0))

			Call result.SetValue("CourseName",doc.CourseName(0))

			Call result.SetValue("CourseID",doc.CourseID(0))

			Call result.SetValue("CourseOffering",doc.CourseOffering(0))

			Call result.SetValue("ClassSection",doc.ClassSection(0))

			Call result.SetValue("ClassNumber",doc.ClassNumber(0))

			Call result.SetValue("Mentorname",doc.MentorName(0))

			Call result.SetValue("MentorID",doc.MentorID(0))

			result.UpdateRow

			i=i+1

		Next 

		Set doc=docColl.getnextdocument(doc)

	Loop		

	result.Close(DB_CLOSE)

	con.Disconnect 

End If

End Sub

2

Subject: Export to MSSQL problem - Resolved

Thanks everyone for pointing out a few things. I realised the issue with the i loop and had that rectified. I moved the result.execute loop outside the For loop and everything work perfectly.

Regards

3

Subject: Export to MSSQL problem

Place “Print result.GetExtendedErrorMessage” statement in some places in your code to find out what error you get.

4

Subject: Export to MSSQL problem

Question was the one record the first or the last?

Try moving the result.execute line to before the for/next statement. I haven’t tested this, but I think you’re resetting the pointer by including it in the loop.

You could always try the insert SQL method:

INSERT [INTO] table_or_view [(column_list)] data_values

so that you collect your data values and build a new qry statement:

qry={INSERT INTO MyTable (PriKey, Description)

   VALUES (123, 'A description of part 123.')}
5

Subject: Export to MSSQL problem

In addition to the other two suggestions, try moving the result.Execute outside of your For loop. You shouldn’t need to execute the query in each iteration of the addrow processing loop. I’m not sure what effect that would have on your results, if any.

And on a side note: you don’t need to increment the counter in a For loop unless you purposely want to skip entries.

hope that helps.