LSXLC Notes coding Question

hcl-bot · March 2, 2005, 3:52pm

Hello.

I am experimeting with using LSXLC so a Notes db can access an SQL Server. What I want to be able to do is:

run a query to extract certain codes from a table
use the codes returned above in another query and get all the records related to those codes.

I have my agent which sucessfully retunrs the codes I require but when I try extracting the records for those codes I get the following error message:

Error: Field Count mismatch, connector ‘oledb’, Method -Fetch-

Can anyone please assist me with this. I am at a loss as to why it is happening.

My code is:

Sub Initialize

 ' this works...

 

 Dim session As New LCSession

 

 ' set-up connection to get ACTOR_IDs

 '--------------------------------------------------

 Dim connect_actor_ids As New LCConnection("oledb")

 connect_actor_ids.Provider = "SQLOLEDB"

 connect_actor_ids.Init_Datasource = "xx"

 connect_actor_ids.Database = "xx"

 connect_actor_ids.Userid = "xx"

 connect_actor_ids.Password = "xx"

 connect_actor_ids.Connect

 

 Dim fldLst As New LCFieldList     

 Dim fldactorId As LCField

 

 Dim sql_actor_ids As String

 sql_actor_ids = "select Name3_Actor_id from dbo.PAT_REPORT_TABLE "

 sql_actor_ids = sql_actor_ids & "Group By Name3_Actor_id "

 sql_actor_ids = sql_actor_ids & "Having Count(*) > 500 "

 sql_actor_ids = sql_actor_ids & "order by Name3_Actor_id ASC"     

 If (connect_actor_ids.Execute(sql_actor_ids, fldLst) = 0) Then

      Msgbox "No records were found"

      connect_actor_ids.Disconnect          

      '**************************************************************

      ' send email to IT that Extranet has failed to run properly...

      '**************************************************************

      Exit Sub 

 End If

 

 Set fldactorId = fldLst.GetField(1)     

 connect_actor_ids.MapByName = True

 

 ' set-up connection to get data

 '----------------------------------------

 Dim connect_pat_data As New LCConnection("oledb")

 connect_pat_data.Provider = "SQLOLEDB"

 connect_pat_data.Init_Datasource = "xxxx"

 connect_pat_data.Database = "xxxx"

 connect_pat_data.Userid = "xxxx"

 connect_pat_data.Password = "xxxx"

 connect_pat_data.Connect

 

 Dim fldLst_connect_pat_data As New LCFieldList     

 

 Dim fld_casenumber_connect_pat_data As LCField

 Dim fld_clientref_connect_pat_data As LCField

 Dim fld_statename_connect_pat_data As LCField

 Dim fld_casecatchword_connect_pat_data As LCField

 Dim fld_name1name_connect_pat_data As LCField

 Dim fld_applno_connect_pat_data As LCField

 Dim fld_appldate_connect_pat_data As LCField

 Dim fld_regno_connect_pat_data As LCField

 Dim fld_regdate_connect_pat_data As LCField

 Dim fld_priono_connect_pat_data As LCField

 Dim fld_statuslabel_connect_pat_data As LCField

 Dim fld_renewaldate_connect_pat_data As LCField

 

 Dim sql_pat_data_part1 As String

 Dim sql_pat_data_part2 As String

 Dim sql_pat_data_part3 As String

 Dim sql_pat_data_all As String

 

 sql_pat_data_part1 = "select "

 sql_pat_data_part1 = sql_pat_data_part1 & "pat_report_table.name1_name, "

 sql_pat_data_part1 = sql_pat_data_part1 & "pat_report_table.case_number, "

 sql_pat_data_part1 = sql_pat_data_part1 & "pat_report_table.client_ref, "

 sql_pat_data_part1 = sql_pat_data_part1 & "state_name.state_name, "

 sql_pat_data_part1 = sql_pat_data_part1 & "pat_case.case_catch_word, "

 sql_pat_data_part1 = sql_pat_data_part1 & "pat_report_table.appl_no, "

 sql_pat_data_part1 = sql_pat_data_part1 & "pat_report_table.appl_date, "

 sql_pat_data_part1 = sql_pat_data_part1 & "pat_report_table.reg_no, "

 sql_pat_data_part1 = sql_pat_data_part1 & "pat_report_table.reg_date, "

 sql_pat_data_part1 = sql_pat_data_part1 & "pat_report_table.prio_no, "

 sql_pat_data_part1 = sql_pat_data_part1 & "pat_status_text.status_label, "

 sql_pat_data_part1 = sql_pat_data_part1 & "pat_report_table.renewal_date  "

 sql_pat_data_part1 = sql_pat_data_part1 & "from "

 sql_pat_data_part1 = sql_pat_data_part1 & "pat_report_table, "

 sql_pat_data_part1 = sql_pat_data_part1 & "state_name, "

 sql_pat_data_part1 = sql_pat_data_part1 & "pat_case, "

 sql_pat_data_part1 = sql_pat_data_part1 & "pat_status_text "

 sql_pat_data_part1 = sql_pat_data_part1 & "where "

 sql_pat_data_part1 = sql_pat_data_part1 & "pat_case.case_id = pat_report_table.case_id "

 sql_pat_data_part1 = sql_pat_data_part1 & "and "

 sql_pat_data_part1 = sql_pat_data_part1 & "pat_case.state_id = state_name.state_id "

 sql_pat_data_part1 = sql_pat_data_part1 & "and "

 sql_pat_data_part1 = sql_pat_data_part1 & "pat_case.status_id = pat_status_text.status_id "

 sql_pat_data_part1 = sql_pat_data_part1 & "and "

 sql_pat_data_part1 = sql_pat_data_part1 & "((pat_case.case_closed is null) "

 sql_pat_data_part1 = sql_pat_data_part1 & "and "

 sql_pat_data_part1 = sql_pat_data_part1 & "(state_name.language_id = 3) "

 sql_pat_data_part1 = sql_pat_data_part1 & "and "

 

 sql_pat_data_part3 = sql_pat_data_part3 & "order by "

 sql_pat_data_part3 = sql_pat_data_part3 & "pat_report_table.name1_name asc"

 

 '-------------------------

 ' processing data...

 '-------------------------

 Dim msg As String

 msg = "Fetching Actor ID" & Chr(13)     

 Dim count As Integer

 count = 0

 

 While(connect_actor_ids.Fetch(fldLst,1,1) > 0)     

      

      If fldactorId.Text(0) <> "" Then

           

           

           

      ' for every actor_id get it's cases...

      '--------------------------------------------          

           count = count + 1

           sql_pat_data_part2 = " (pat_report_table.name3_actor_id in ('" & fldactorId.Text(0) & "'))) "

           

           sql_pat_data_all = sql_pat_data_part1 & sql_pat_data_part2  & sql_pat_data_part3

           Messagebox "sql_pat_data_all = " & sql_pat_data_all

           

           If (connect_pat_data.Execute(sql_pat_data_all, fldLst_connect_pat_data) = 0) Then

                Msgbox "No Patricia Data records were found"

                connect_pat_data.Disconnect          

           '**************************************************************

           ' send email to IT that Extranet has failed to run properly...

           '**************************************************************

                Exit Sub 

           End If

'%REM

           Messagebox "next while loop"

           While(connect_pat_data.Fetch(fldLst_connect_pat_data,1,1) > 0)

                          ******** the following messageboxes are never executed?????????     

                Messagebox "1"

                Set fld_casenumber_connect_pat_data = fldLst_connect_pat_data.GetField(1)

                Messagebox "2"

                Set fld_clientref_connect_pat_data = fldLst_connect_pat_data.GetField(2)

                Messagebox "3"

                Set fld_statename_connect_pat_data = fldLst_connect_pat_data.GetField(3)

                Messagebox "4"

                Set fld_casecatchword_connect_pat_data = fldLst_connect_pat_data.GetField(4)

                Messagebox "5"

                Set fld_name1name_connect_pat_data = fldLst_connect_pat_data.GetField(5)

                Messagebox "6"

                Set fld_applno_connect_pat_data = fldLst_connect_pat_data.GetField(6)

                Messagebox "7"

                Set fld_appldate_connect_pat_data = fldLst_connect_pat_data.GetField(7)

                Messagebox "8"

                Set fld_regno_connect_pat_data = fldLst_connect_pat_data.GetField(8)

                Messagebox "9"

                Set fld_regdate_connect_pat_data = fldLst_connect_pat_data.GetField(9)

                Messagebox "10"

                Set fld_priono_connect_pat_data = fldLst_connect_pat_data.GetField(10)

                Messagebox "11"

                Set fld_statuslabel_connect_pat_data = fldLst_connect_pat_data.GetField(11)

                Messagebox "12"

                Set fld_renewaldate_connect_pat_data = fldLst_connect_pat_data.GetField(12)

                Messagebox "13"

                

                connect_pat_data.MapByName = True

                

                connect_pat_data.Disconnect     

                

           Wend

'%END REM

           Print Cstr(count)

           msg = msg & Cstr(count)

           msg = msg & ". " & fldactorId.Text(0)          

           msg = msg & Chr(13)     

      End If

 Wend

 

 

 Messagebox msg     

 

 'Messagebox "Done"

 

 ' close the connections we have open...

 connect_actor_ids.Disconnect

 connect_pat_data.Disconnect

End Sub

The message

Messagebox “next while loop”

is displayed twice before the error message is displayed and none of the messageboxes prompts are displayed i.e. the code never goes into the second while loop.

Kind Regards

NM

hcl-bot · March 2, 2005, 6:28pm

Subject: LSXLC Notes coding Question

it’s OK. I got it sorted out. had my coding all screwy…

The following works:

Sub Initialize

' this works...



Dim session As New LCSession



Dim fldLst As New LCFieldList	

Dim fldLst2 As New LCFieldList	

Dim fldLst3 As New LCFieldList	

Dim fldLst4 As New LCFieldList	



' set-up connection to get ACTOR_IDs

'--------------------------------------------------

Dim connect_actor_ids As New LCConnection("oledb")

connect_actor_ids.Provider = "SQLOLEDB"

connect_actor_ids.Init_Datasource = "Freddie2"

connect_actor_ids.Database = "xxx"

connect_actor_ids.Userid = "xxx"

connect_actor_ids.Password = "xx"

connect_actor_ids.Connect	



Dim sql_actor_ids As String

sql_actor_ids = "select Name3_Actor_id from dbo.PAT_REPORT_TABLE "

sql_actor_ids = sql_actor_ids & "Group By Name3_Actor_id "

sql_actor_ids = sql_actor_ids & "Having Count(*) > 500 "

sql_actor_ids = sql_actor_ids & "order by Name3_Actor_id ASC"	

If (connect_actor_ids.Execute(sql_actor_ids, fldLst) = 0) Then

	Msgbox "No records were found"

	connect_actor_ids.Disconnect		

	'**************************************************************

	' send email to IT that Extranet has failed to run properly...

	'**************************************************************

	Exit Sub 

End If	



Dim fldactorId As LCField

Set fldactorId = fldLst.GetField(1)	

connect_actor_ids.MapByName = True



' set-up connection to get data

'----------------------------------------

Dim connect_pat_data As New LCConnection("oledb")

connect_pat_data.Provider = "SQLOLEDB"

connect_pat_data.Init_Datasource = "Freddie2"

connect_pat_data.Database = "xxx"

connect_pat_data.Userid = "xxx"

connect_pat_data.Password = "xxx"

connect_pat_data.Connect



Dim fld_casenumber_connect_pat_data As LCField

Dim fld_clientref_connect_pat_data As LCField

Dim fld_statename_connect_pat_data As LCField

Dim fld_casecatchword_connect_pat_data As LCField

Dim fld_name1name_connect_pat_data As LCField

Dim fld_applno_connect_pat_data As LCField

Dim fld_appldate_connect_pat_data As LCField

Dim fld_regno_connect_pat_data As LCField

Dim fld_regdate_connect_pat_data As LCField

Dim fld_priono_connect_pat_data As LCField

Dim fld_statuslabel_connect_pat_data As LCField

Dim fld_renewaldate_connect_pat_data As LCField



Dim sql_pat_data_part1 As String

Dim sql_pat_data_part2 As String

Dim sql_pat_data_part3 As String

Dim sql_pat_data_all As String



sql_pat_data_part1 = "select "

sql_pat_data_part1 = sql_pat_data_part1 & "pat_report_table.name1_name As name1_name, "

sql_pat_data_part1 = sql_pat_data_part1 & "pat_report_table.case_number As case_number, "

sql_pat_data_part1 = sql_pat_data_part1 & "pat_report_table.client_ref As client_ref, "

sql_pat_data_part1 = sql_pat_data_part1 & "state_name.state_name As state_name, "

sql_pat_data_part1 = sql_pat_data_part1 & "pat_case.case_catch_word As catch_word, "

sql_pat_data_part1 = sql_pat_data_part1 & "pat_report_table.appl_no As appl_no, "

sql_pat_data_part1 = sql_pat_data_part1 & "pat_report_table.appl_date As appl_date, "

sql_pat_data_part1 = sql_pat_data_part1 & "pat_report_table.reg_no As reg_no, "

sql_pat_data_part1 = sql_pat_data_part1 & "pat_report_table.reg_date As reg_date, "

sql_pat_data_part1 = sql_pat_data_part1 & "pat_report_table.prio_no As prio_no, "

sql_pat_data_part1 = sql_pat_data_part1 & "pat_status_text.status_label As status_label, "

sql_pat_data_part1 = sql_pat_data_part1 & "pat_report_table.renewal_date As renewal_date  "

sql_pat_data_part1 = sql_pat_data_part1 & "from "

sql_pat_data_part1 = sql_pat_data_part1 & "pat_report_table, "

sql_pat_data_part1 = sql_pat_data_part1 & "state_name, "

sql_pat_data_part1 = sql_pat_data_part1 & "pat_case, "

sql_pat_data_part1 = sql_pat_data_part1 & "pat_status_text "

sql_pat_data_part1 = sql_pat_data_part1 & "where "

sql_pat_data_part1 = sql_pat_data_part1 & "pat_case.case_id = pat_report_table.case_id "

sql_pat_data_part1 = sql_pat_data_part1 & "and "

sql_pat_data_part1 = sql_pat_data_part1 & "pat_case.state_id = state_name.state_id "

sql_pat_data_part1 = sql_pat_data_part1 & "and "

sql_pat_data_part1 = sql_pat_data_part1 & "pat_case.status_id = pat_status_text.status_id "

sql_pat_data_part1 = sql_pat_data_part1 & "and "

sql_pat_data_part1 = sql_pat_data_part1 & "((pat_case.case_closed is null) "

sql_pat_data_part1 = sql_pat_data_part1 & "and "

sql_pat_data_part1 = sql_pat_data_part1 & "(state_name.language_id = 3) "

sql_pat_data_part1 = sql_pat_data_part1 & "and "	

sql_pat_data_part3 = sql_pat_data_part3 & "order by "

sql_pat_data_part3 = sql_pat_data_part3 & "pat_report_table.name1_name asc"



'-------------------------

' processing data...

'-------------------------

Dim msg As String

msg = "Fetching Actor ID" & Chr(13)	

Dim count As Integer

count = 0



While(connect_actor_ids.Fetch(fldLst,1,1) > 0)	

	

	If fldactorId.Text(0) <> "" Then

		

	' for every actor_id get it's cases...

	'--------------------------------------------		

		count = count + 1

		sql_pat_data_part2 = " (pat_report_table.name3_actor_id in ('" & fldactorId.Text(0) & "'))) "

		

		sql_pat_data_all = sql_pat_data_part1 & sql_pat_data_part2  & sql_pat_data_part3

		Messagebox "sql_pat_data_all = " & sql_pat_data_all

		Print Cstr(count)

		If (connect_pat_data.Execute(sql_pat_data_all, fldLst3) = 0) Then

			Msgbox "No Patricia Data records were found"

			connect_pat_data.Disconnect		

		'**************************************************************

		' send email to IT that Extranet has failed to run properly...

		'**************************************************************

			Exit Sub 

		End If

		

		Call fldLst4.Map(fldLst3, "name1_name, case_number, client_ref, state_name, catch_word, appl_no, appl_date, reg_no, reg_date, prio_no, status_label, renewal_date")	

		

		

		Set fld_casenumber_connect_pat_data = fldLst4.GetField(1)

		Set  fld_clientref_connect_pat_data = fldLst4.GetField(2)

		Set  fld_statename_connect_pat_data = fldLst4.GetField(3)

		Set  fld_casecatchword_connect_pat_data = fldLst4.GetField(4)

		Set  fld_name1name_connect_pat_data = fldLst4.GetField(5)

		Set  fld_applno_connect_pat_data = fldLst4.GetField(6)

		Set  fld_appldate_connect_pat_data = fldLst4.GetField(7)

		Set  fld_regno_connect_pat_data = fldLst4.GetField(8)

		Set  fld_regdate_connect_pat_data = fldLst4.GetField(9)

		Set  fld_priono_connect_pat_data = fldLst4.GetField(10)

		Set  fld_statuslabel_connect_pat_data = fldLst4.GetField(11)

		Set  fld_renewaldate_connect_pat_data = fldLst4.GetField(12)	

		connect_actor_ids.MapByName = True	

		

		Dim patDataCount As Integer

		patDataCount = 0

		While(connect_pat_data.Fetch(fldLst4,1,1) > 0)	

			patDataCount = patDataCount + 1

			Print fldactorId.Text(0) & " : " & Cstr(patDataCount)

		Wend		

		

	End If

Wend





Messagebox msg	



' close the connections we have open...

connect_actor_ids.Disconnect

connect_pat_data.Disconnect

End Sub

hcl-bot · March 2, 2005, 8:13pm

Subject: RE: LSXLC Notes coding Question

A complete end-to-end working example! If only the lclsx help file had more of those.

Another way to do the same thing is to use the Lookup method:

Set lccon = New LCConnection(“oledb”)

With lccon

...

.Fieldnames = "field1, field2"

...

End With

lccon.Connect

statement = “SELECT field1, field2 FROM sometable where field1=‘00011’”

Set lcresultfieldlist = New LCFieldList

Call lccon.Execute(statement, lcresultfieldlist)

Set lc_field1 = lcresultfieldlist.Lookup(“field1”)

Set lc_field2 = lcresultfieldlist.Lookup(“field2”)

While (lccon.Fetch (lcresultfieldlist) > 0)

…