Database

procedure FetchSL3Details_PRC
(
  c_items out T_CURSOR,
  c_identifiers out T_CURSOR,
  p_niin in char
) as
  v_idents IDENTIFIER_TABLE_TYPE:= IDENTIFIER_TABLE_TYPE();
  BEGIN
    
    open c_items for
      select
        its.item_set_id,
        its.niin,
        its.parent_niin,
        its.commodity_id,
        its.service_type,
        its.sl3_type,
        its.qty,
        its.created_id,
        its.created_dt,
        its.modified_id,
        its.modified_dt
      from
        item_set its
      start with its.niin = p_niin
      connect by prior its.niin = its.parent_niin;
      
      for item in c_items
      loop
        v_idents.extend;
        v_idents(v_idents.LAST) := identifier_row_type(item.commodity_id,
                                                          get_group_name_fun(item.commodity_id),
                                                          0);
        v_idents.extend;
        v_idents(v_idents.LAST) := identifier_row_type(item.created_id,
                                                          get_formatted_name_fun(item.created_id),
                                                          0);
        v_idents.extend;
        v_idents(v_idents.LAST) := identifier_row_type(item.modified_id,
                                                          get_formatted_name_fun(item.modified_id),
                                                          0);
      end loop;
      
      open c_identifiers for
        select 
          distinct(v.id),
          v.name,
          v.type
        from
          v_idents v;
    
  END FetchSL3Details_PRC;

Public Function UpdateRecord2(ByVal strUpdate As String, params() As String) As Boolean
        Dim RecordsUpdated As Integer = 0
        Dim cnStr As String = "Provider=" & Provider & "; Data source= " & _Database

        Using cn As New OleDb.OleDbConnection(cnStr)
            Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
            Dim cmd As OleDb.OleDbCommand
            cmd = New OleDb.OleDbCommand(strUpdate, cn)
            For i As Integer = 0 To params.Length - 1
                If params(i) = Nothing Then params(i) = ""
                cmd.Parameters.AddWithValue("?", params(i))
            Next
            da.InsertCommand = cmd
            Try
                cn.Open()
                RecordsUpdated = da.InsertCommand.ExecuteNonQuery()
                cn.Close()
            Catch ex As Exception
                frmMain.Err.DataMethod = "UpdateRecord"
                frmMain.Err.DataErr = ex.Message
                Return False
            Finally
                cn.Close()
            End Try
        End Using

        If RecordsUpdated = 1 Then
            Return True
        Else
            frmMain.Err.DataMethod = "UpdateRecord"
            frmMain.Err.DataErr = "No Records updated"
            Return False
        End If

    End Function